Loading...
 

MS SQL Server Snippets

Drop If Exists - Normal Table

  • 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

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

Change a Nullable column to NOT NULL with Default Value

  • 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
  • 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

Move database files

  • Get the logical name of the data and log files associated with the database by typing the following
    • 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"
    • 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
    • ALTER DATABASE Personnel SET offline
  • Move the physical files
  • Bring database back online
    • ALTER DATABASE Personnel SET online

List Databases Part of Availability Group

  • 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

  • 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

  • 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

  • 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

  • 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
    • EXEC master..Xp_readerrorlog
  • Filter error log
    • 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
    • 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)
    • EXEC sp_readerrorlog 0, 2, 'SQLServerAgent starting'