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

  • http://gallery.technet.microsoft.com/scriptcenter/All-Databases-Data-log-a36da95d
  • ------------------------------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
    • 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'