Loading...
 

MS SQL Server Snippets

Home

Drop If Exists - Normal Table

  • Copy to clipboard
    IF EXISTS (SELECT 1 FROM DATABASE_NAME.information_schema.tables WHERE table_name = 'TABLE_NAME' AND table_schema = 'dbo') DROP TABLE DATABASE_NAME.SCHEMA.TABLE_NAME

 

Drop If Exists - Temp Table

  • Copy to clipboard
    IF OBJECT_ID('tempdb..#TABLE_NAME') IS NOT NULL DROP TABLE #TABLE_NAME

 

Change a Nullable column to NOT NULL with Default Value

  • Copy to clipboard
    ALTER TABLE dbo.MyTable ADD CONSTRAINT my_Con DEFAULT GETDATE() for created ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL

 

Get list of all tables and row counts

  • Also good to check progress of a insert - shows count before an insert is complete
  • Copy to clipboard
    SELECT c.name +'.'+ a.name TableName, SUM (b.rows) RowCount FROM sys.tables a INNER JOIN sys.partitions b ON b.OBJECT_ID = a.OBJECT_ID INNER JOIN sys.schemas c ON a.schema_id = c.schema_id WHERE a.is_ms_shipped = 0 AND b.index_id IN (1, 0) -- AND a.name in ('') GROUP BY c.name, a.name ORDER BY SUM (b.rows) DESC

 

All Databases Data & log file size, space used & free space

  • http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d
  • Copy to clipboard
    ------------------------------Data file size---------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') drop table #dbsize create table #dbsize (Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0)) go insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) , CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')), sum(size)/128.0 AS File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB from sys.database_files where type=0 group by type' go -------------------log size-------------------------------------- if exists (select * from tempdb.sys.all_objects where name like '#logsize%') drop table #logsize create table #logsize (Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0)) go insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB) exec sp_msforeachdb 'use [?]; select DB_NAME() AS DbName, sum(size)/128.0 AS Log_File_Size_MB, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB, SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB from sys.database_files where type=1 group by type' go --------------------------------database free size if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%') drop table #dbfreesize create table #dbfreesize (name sysname, database_size varchar(50), Freespace varchar(50)default (0.00)) insert into #dbfreesize(name,database_size,Freespace) exec sp_msforeachdb 'use [?];SELECT database_name = db_name() ,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'') ,''unallocated space'' = ltrim(str(( CASE WHEN dbsize >= reservedpages THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576 ELSE 0 END ), 15, 2) + '' MB'') FROM ( SELECT dbsize = sum(convert(BIGINT, CASE WHEN type = 0 THEN size ELSE 0 END)) ,logsize = sum(convert(BIGINT, CASE WHEN type <> 0 THEN size ELSE 0 END)) FROM sys.database_files ) AS files ,( SELECT reservedpages = sum(a.total_pages) ,usedpages = sum(a.used_pages) ,pages = sum(CASE WHEN it.internal_type IN ( 202 ,204 ,211 ,212 ,213 ,214 ,215 ,216 ) THEN 0 WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id ) AS partitions' ----------------------------------- if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%') drop table #alldbstate create table #alldbstate (dbname sysname, DBstatus varchar(55), R_model Varchar(30)) --select * from sys.master_files insert into #alldbstate (dbname,DBstatus,R_model) select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases --select * from #dbsize insert into #dbsize(Dbname,dbstatus,Recovery_Model) select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online' insert into #logsize(Dbname) select dbname from #alldbstate where DBstatus <> 'online' insert into #dbfreesize(name) select dbname from #alldbstate where DBstatus <> 'online' select d.Dbname,d.dbstatus,d.Recovery_Model, (file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace from #dbsize d join #logsize l on d.Dbname=l.Dbname join #dbfreesize fs on d.Dbname=fs.name order by Dbname

 

Move database files

  • Get the logical name of the data and log files associated with the database by typing the following
    • Copy to clipboard
      SELECT b.name as DatabaseName, a.name as LogicalName, a.physical_name as PhysicalName FROM sys.master_files a INNER JOIN sys.databases b ON a.database_id = b.database_id ORDER BY b.name, a.name
  • Tell SQL Server where you will move the file
    • This does not actually move anything, and SQL Server does not look at this new location until next time the database is "restarted"
    • Copy to clipboard
      ALTER DATABASE Personnel MODIFY FILE ( NAME = Personnel_Data, FILENAME = "C:\Data\Personnel_Data.mdf")
  • Take database offline
    • This leaves the database in the tree
    • Releases SQL Server lock on the physical files
    • Copy to clipboard
      ALTER DATABASE Personnel SET offline
  • Move the physical files
  • Bring database back online
    • Copy to clipboard
      ALTER DATABASE Personnel SET online

 

List Databases Part of Availability Group

  • Copy to clipboard
    SELECT dbcs.database_name AS [DatabaseName] FROM master.sys.availability_groups AS AG LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates ON AG.group_id = agstates.group_id INNER JOIN master.sys.availability_replicas AS AR ON AG.group_id = AR.group_id INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1 INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs ON arstates.replica_id = dbcs.replica_id LEFT OUTER JOIN master.sys.dm_hadr_database_replica_states AS dbrs ON dbcs.replica_id = dbrs.replica_id AND dbcs.group_database_id = dbrs.group_database_id WHERE ISNULL (arstates.role, 3) = 2 AND ISNULL (dbcs.is_database_joined, 0) = 1 AND d.name = dbcs.database_name

 

Exit from Stored Procedure

  • Copy to clipboard
    RETURN [ integer_expression ]
  • Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

 

List Tables, columns, datatypes, constraints, related columns

  • Copy to clipboard
    SELECT aa.TABLE_CATALOG as DATABASE_NAME , aa.TABLE_SCHEMA , aa.TABLE_NAME , aa.COLUMN_NAME , aa.ORDINAL_POSITION , ISNULL (REPLACE (REPLACE (aa.COLUMN_DEFAULT, CHAR(10), ''), CHAR(13), ''), '') as COLUMN_DEFAULT , aa.IS_NULLABLE , aa.DATA_TYPE , ISNULL (CAST (CHARACTER_MAXIMUM_LENGTH as varchar), '') as CHARACTER_LENGTH , ISNULL (a.CONSTRAINT_NAME, '') as CONSTRAINT_NAME , ISNULL (b.CONSTRAINT_TYPE, '') AS CONSTRAINT_TYPE , (SELECT ISNULL (CAST (MAX (x.ORDINAL_POSITION) as varchar), '') FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE x WHERE c.CONSTRAINT_CATALOG = x.CONSTRAINT_CATALOG AND c.CONSTRAINT_SCHEMA = x.CONSTRAINT_SCHEMA AND c.CONSTRAINT_NAME = x.CONSTRAINT_NAME ) as NUMBER_COLUMNS_IN_CONSTRAINT , ISNULL (c.TABLE_SCHEMA, '') as REFERENCED_TABLE_SCHEMA , ISNULL (c.TABLE_NAME, '') as REFERENCED_TABLE_NAME , ISNULL (c.COLUMN_NAME, '') as REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS aa LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a ON aa.TABLE_CATALOG = a.TABLE_CATALOG AND aa.TABLE_SCHEMA = a.TABLE_SCHEMA AND aa.TABLE_NAME = a.TABLE_NAME AND aa.COLUMN_NAME = a.COLUMN_NAME LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.TABLE_CATALOG = b.CONSTRAINT_CATALOG AND a.TABLE_SCHEMA = b.CONSTRAINT_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c ON a.TABLE_CATALOG = c.CONSTRAINT_CATALOG AND a.TABLE_SCHEMA = c.CONSTRAINT_SCHEMA AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME ORDER BY aa.TABLE_CATALOG, aa.TABLE_SCHEMA, aa.TABLE_NAME, aa.ORDINAL_POSITION, a.CONSTRAINT_NAME, a.COLUMN_NAME, c.TABLE_NAME, c.COLUMN_NAME


 

List table names; create, access, update date; number columns and rows; space in total, table, and indexes

  • Copy to clipboard
    SELECT b.name + '.' + a.name as tableName , a.create_date as createDate , c.lastAccessDate , c.lastUpdateDate , a.max_column_id_used as numberOfColumns , d.numberOfRows , e.totalSpaceReservedKB , e.totalSpaceUsedKB , e.totalSpaceUnusedKB , e.numberOfIndexes , e.tableSpaceReservedKB , e.tableSpaceUsedKB , e.tableSpaceUnusedKB , e.indexSpaceReservedKB , e.indexSpaceUsedKB , e.indexSpaceUnusedKB FROM sys.tables a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id LEFT OUTER JOIN (SELECT x.object_id , OBJECT_NAME(x.object_id) as tableName , (SELECT MAX (inlineTable.d) FROM (VALUES (MAX (x.last_user_seek)) , (MAX (x.last_user_scan)) , (MAX (x.last_user_lookup)) ) as inlineTable (d) ) as lastAccessDate , MAX (last_user_update) as lastUpdateDate FROM sys.dm_db_index_usage_stats AS x WHERE x.database_id = DB_ID() AND x.object_id = OBJECT_ID('testLastAccess') GROUP BY x.object_id, OBJECT_NAME(x.object_id) ) c ON a.object_id = c.object_id LEFT OUTER JOIN (SELECT x.object_id , z.name + '.' + x.name as tableName , SUM (y.rows) as numberOfRows FROM sys.tables x INNER JOIN sys.partitions y ON x.OBJECT_ID = y.OBJECT_ID INNER JOIN sys.schemas z ON x.schema_id = z.schema_id WHERE x.is_ms_shipped = 0 AND y.index_id IN (1, 0) GROUP BY x.object_id, z.name, x.name ) d ON a.object_id = d.object_id LEFT OUTER JOIN (SELECT r.name + '.' + m.NAME AS tableName , m.object_id , SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') THEN 1 ELSE 0 END) as numberOfIndexes , SUM (q.total_pages) * 8 AS totalSpaceReservedKB , SUM (q.used_pages) * 8 AS totalSpaceUsedKB , (SUM (q.total_pages) - SUM(q.used_pages)) * 8 AS totalSpaceUnusedKB , SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') THEN q.total_pages ELSE 0 END) * 8 AS tableSpaceReservedKB , SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') THEN q.used_pages ELSE 0 END) * 8 AS tableSpaceUsedKB , (SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') THEN q.total_pages ELSE 0 END) - SUM (CASE WHEN n.type_desc IN ('CLUSTERED', 'HEAP') THEN q.used_pages ELSE 0 END)) * 8 AS tableSpaceUnusedKB , SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') THEN q.total_pages ELSE 0 END) * 8 AS indexSpaceReservedKB , SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') THEN q.used_pages ELSE 0 END) * 8 AS indexSpaceUsedKB , (SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') THEN q.total_pages ELSE 0 END) - SUM (CASE WHEN n.type_desc IN ('NONCLUSTERED') THEN q.used_pages ELSE 0 END)) * 8 AS indexSpaceUnusedKB FROM sys.tables m INNER JOIN sys.indexes n ON m.OBJECT_ID = n.object_id INNER JOIN sys.partitions p ON n.object_id = p.OBJECT_ID AND n.index_id = p.index_id INNER JOIN sys.allocation_units q ON p.partition_id = q.container_id LEFT OUTER JOIN sys.schemas r ON m.schema_id = r.schema_id WHERE m.NAME NOT LIKE 'dt%' AND m.is_ms_shipped = 0 AND n.OBJECT_ID > 255 GROUP BY m.object_id, m.Name, r.Name, p.Rows ) e ON a.object_id = e.object_id WHERE a.NAME NOT LIKE 'dt%' AND a.is_ms_shipped = 0 AND a.OBJECT_ID > 255 ORDER BY b.name + '.' + a.name

 

List size used by indexes

  • Copy to clipboard
    SELECT r.name + '.' + m.NAME AS tableName , m.object_id , n.type_desc as indexType , n.index_id , n.name as indexName , SUM (q.total_pages) * 8 AS indexSpaceReservedKB , SUM (q.used_pages) * 8 AS indexSpaceUsedKB , (SUM (q.total_pages) - SUM (q.used_pages)) * 8 AS indexSpaceUnusedKB FROM sys.tables m INNER JOIN sys.indexes n ON m.OBJECT_ID = n.object_id INNER JOIN sys.partitions p ON n.object_id = p.OBJECT_ID AND n.index_id = p.index_id INNER JOIN sys.allocation_units q ON p.partition_id = q.container_id LEFT OUTER JOIN sys.schemas r ON m.schema_id = r.schema_id WHERE m.NAME NOT LIKE 'dt%' AND m.is_ms_shipped = 0 AND n.OBJECT_ID > 255 GROUP BY m.object_id, m.Name, n.index_id, n.type_desc, n.name, r.Name, p.Rows ORDER BY r.name, m.name, n.type_desc, n.name

 

Error log

  • Display error log
    • Copy to clipboard
      EXEC master..Xp_readerrorlog
  • Filter error log
    • Copy to clipboard
      EXEC master..Xp_readerrorlog 0, 1, N'FINDME'

 

Get SQL Server Agent Startup Time

  • Can't confirm this is 100.0% perfect, but seems to work at least 99.9% of time from online searches
    • Copy to clipboard
      SELECT login_time FROM sys.sysprocesses WHERE program_name LIKE 'SQLAgent - Generic Refresher%'
  • If SQL Server Agent log hasn't been cycled, otherwise, change the "0" to a higher number, up to 9 (more after 2008, I think)
    • Copy to clipboard
      EXEC sp_readerrorlog 0, 2, 'SQLServerAgent starting'