Category: SQL Server

  • Script to determine identity table and identity column

    Script to determine the identity tables and columns in the databases.

    USE <DBNAME>
--script to determine the table with the identity column
select name, OBJECTPROPERTY(id, 'TableHasIdentity') AS TableHasIdentityCol
from sysobjects
where xtype = 'U'

--script to determine the table and columne with the identity on
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE status = 0x80

--script to determine the table and columne with the identity on using ColumnProperty
SELECT OBJECT_NAME(id) as TblName, name as ColName
FROM syscolumns
WHERE COLUMNPROPERTY(id, name, 'IsIdentity') = 1

  • T-SQL Script to identify the data, log and backup drive

    You can use the below script to identify the data, log and backup file drive.

    select 
@@SERVERNAME as svrName,
drivename, 
drivedescription
from
(
select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
union
select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
union 
select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
) tab1

  • Script to find out the database file size, log file size on SQL Server 2000/2005/2008

    Recently I encountered a situation where i have to find out the database file size on SQL Server 2000. Here is the script which you can use to run on SQL Server 2000. Below script will on SQL Server 2005 or SQL Server 2008 as well.

    You can use sys.master_files instead of sysAltfiles in SQL Server 2005 and SQL Server 2008. Please find all the different scripts below.

    --SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000

select 
fileID 
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

  • Saving changes is not permitted while modifying table design

    Problem: There may be scenario while modifying the table design which needs to re-create the table. Even with the full permission while modifying it from SSMS, you get the below message and your table changes will be rolled back.

    Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

    Solution:
    Below are the few scenarios where changes in table design will re-create the table.

    Changing the column data type
    Changing the NULL property
    Changing the order of the columns
    Adding column before the last column
    Changing the identity column property
    Changing the computed column expression

    Let’s see how can we fix the “Saving Changes is not permitted …”

    Go to SSMS – Select Tools menu -> click on Options -> Go to Designers tab page -> Click on “table and database designers” page -> Un-check “Prevent Saving changes that require table re-creation” check box.