T-SQL Cheat Sheet - Part 1

In this post I put together a basic set of t-sql scripts that give you metadata about MSSQL. If you’ve ever tried getting some of this info through the GUI, I think you’ll be pleasantly surprised how much of it you can pull up instantly.

Exploring the server

Let’s start with queries that give you info about your servers.

Server and instance names

Select @@SERVERNAME as [Server\Instance];

SQL Server version

Select @@VERSION as SQLServerVersion;

SQL Server instance

Select @@ServiceName AS ServiceInstance;

Current DB (the DB the query runs in the context of)

Select DB_NAME() AS CurrentDB_Name;

How long has your SQL Server been running since its last restart? Keep in mind that the system database tempdb gets recreated on every SQL Server restart. Here’s one way to find out when the server last restarted.

Select  @@Servername AS ServerName ,
        create_date AS  ServerStarted ,
        DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM    sys.databases
WHERE   name = 'tempdb';

GO

Linked servers

Linked servers are connections that let SQL Server reach out to other servers for data. Distributed queries can run across different linked servers. It’s useful to know whether your database server is isolated from others, or linked to other servers.

EXEC sp_helpserver;

--OR

EXEC sp_linkedservers;

--OR

SELECT  @@SERVERNAME AS Server ,
        Server_Id AS  LinkedServerID ,
        name AS LinkedServer ,
        Product ,
        Provider ,
        Data_Source ,
        Modify_Date
FROM    sys.servers
ORDER BY name;

GO

List of all databases

First, let’s get a list of all databases on the server. Keep in mind every server has system databases (master, model, msdb, tempdb, and distribution if you use replication). You’ll probably want to exclude those in the following queries.

There are several ways to get a list of all DBs in T-SQL, and below you’ll see some of them. Each method returns a similar result, but with some differences.

EXEC sp_helpdb;

--OR

EXEC sp_Databases;

--OR

SELECT  @@SERVERNAME AS Server ,
        name AS DBName ,
        recovery_model_Desc AS RecoveryModel ,
        Compatibility_level AS CompatiblityLevel ,
        create_date ,
        state_desc
FROM    sys.databases
ORDER BY Name;

--OR

SELECT  @@SERVERNAME AS Server ,
        d.name AS DBName ,
        create_date ,
        compatibility_level ,
        m.physical_name AS FileName
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE   m.[type] = 0 -- data files only
ORDER BY d.name;

GO

Last backup?

Every good dba should check whether they have a recent backup.

Select  @@Servername AS ServerName ,
        d.Name AS DBName ,
        MAX(b.backup_finish_date) AS LastBackupCompleted
FROM    sys.databases d
        LEFT OUTER JOIN msdb..backupset b
                    ON b.database_name = d.name
                       AND b.[type] = 'D'
GROUP BY d.Name
ORDER BY d.Name;

Path to the file with the last backup.

SELECT  @@Servername AS ServerName ,
        d.Name AS DBName ,
        b.Backup_finish_date ,
        bmf.Physical_Device_name
FROM    sys.databases d
        INNER JOIN msdb..backupset b ON b.database_name = d.name
                                        AND b.[type] = 'D'
        INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
        b.Backup_finish_date DESC;

GO

Active user connections

This will only work on SQL Server 2012 and up - in earlier editions the dmv sys.dm_exec_sessions didn’t have the database_id column. To find out which DBs users are currently working in, you can use sp_who.

SELECT  @@Servername AS Server ,
        DB_NAME(database_id) AS DatabaseName ,
        COUNT(database_id) AS Connections ,
        Login_name AS  LoginName ,
        MIN(Login_Time) AS Login_Time ,
        MIN(COALESCE(last_request_end_time, last_request_start_time))
                                                         AS  Last_Batch
FROM    sys.dm_exec_sessions
WHERE   database_id > 0
        AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
         login_name
ORDER BY DatabaseName;

Exploring databases

Most of the queries in this section look “inside” only one DB, so don’t forget to pick the right DB in SSMS or with the use database command. Also remember you can always check what DB context a query will run in with select db_name().

The system table sys.objects is one of the key ones for collecting info about the objects making up your data model.
In the example, U is tables. Try plugging in other type values in the WHERE

USE MyDatabase;
GO

SELECT  *
FROM    sys.objects
WHERE   type = 'U';

Below is a list of object types you can get info about (see the sys.objects documentation on MSDN)

AF = aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or standalone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = assembly (CLR) stored procedure
FN = SQL scalar function
FS = assembly (CLR) scalar function
FT = assembly (CLR) table-valued function
R = rule (old-style, standalone)
RF = replication-filter procedure
S = system base table
SN = synonym
SQ = service queue
TA = assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued function
U = table (user-defined)
UQ = UNIQUE constraint
V = view
X = extended stored procedure
IT = internal table

Database file locations

The physical location of the selected DB, including the main data file (mdf) and the transaction log file (ldf), can be gotten with these queries.

EXEC sp_Helpfile;

--OR

SELECT  @@Servername AS Server ,
        DB_NAME() AS DB_Name ,
        File_id ,
        Type_desc ,
        Name ,
        LEFT(Physical_Name, 1) AS Drive ,
        Physical_Name ,
        RIGHT(physical_name, 3) AS Ext ,
        Size ,
        Growth
FROM    sys.database_files
ORDER BY File_id;

GO

Tables

Sure, Object Explorer in SSMS shows the full list of tables in the selected DB, but some info is harder to get through the GUI than through scripts. The ANSI standard calls for querying the INFORMATION_SCHEMA views, but they won’t give you info about objects that aren’t part of the standard (like triggers, extended procedures, etc.), so it’s better to use SQL Server catalog views.

EXEC sp_tables; -- Remember, this method returns both tables and views

--OR

SELECT  @@Servername AS ServerName ,
        TABLE_CATALOG ,
        TABLE_SCHEMA ,
        TABLE_NAME
FROM     INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME ;

--OR

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS 'TableName' ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.Type = 'U' -- User table
ORDER BY o.name;

--OR

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS TableName,
        t.[Type],
        t.create_date
FROM    sys.tables t
ORDER BY t.Name;

GO

Row count for a table

Tables with a huge number of rows more often have a serious impact on performance.

In SSMS we can right-click any table, open properties on the Storage tab, and see the row count for the table.

It’s pretty tedious to collect this info manually for all tables. Again, if we write SELECT COUNT(*) FROM TABLENAME for every table, that’s a lot of typing.

The script below generates a set of T-SQL statements for getting the row count in every table of the current database.

Select  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
        + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name
        + ';' AS ' Script generator to get counts for all tables'
FROM    sys.objects o
WHERE   o.[type] = 'U'
ORDER BY o.name;

sp_msForEachTable is an undocumented function that “walks” through all tables in a DB and runs a query, substituting the current table name in place of ‘?’. There’s also a similar function sp_msforeachdb, which works at the database level.

CREATE TABLE #rowcount
    ( Tablename VARCHAR(128) ,
      Rowcnt INT );

EXEC sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?'

SELECT  *
FROM    #rowcount
ORDER BY Tablename ,
        Rowcnt;

DROP TABLE #rowcount;

The fastest way to get a row count - the clustered index

Select  @@ServerName AS Server ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,
        OBJECT_NAME(p.object_id) AS TableName ,
        i.Type_Desc ,
        i.Name AS IndexUsedForCounts ,
        SUM(p.Rows) AS Rows
FROM    sys.partitions p
        JOIN sys.indexes i ON i.object_id = p.object_id
                              AND i.index_id = p.index_id
WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
                             -- This is key (1 index per table)
        AND OBJECT_SCHEMA_NAME(p.object_id) &lt;> 'sys'
GROUP BY p.object_id ,
        i.type_desc ,
        i.Name
ORDER BY SchemaName ,
        TableName;

-- OR

-- A similar way to get the row count, but using the DMV dm_db_partition_stats
SELECT  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
        OBJECT_NAME(ddps.object_id) AS TableName ,
        i.Type_Desc ,
        i.Name AS IndexUsedForCounts ,
        SUM(ddps.row_count) AS Rows
FROM    sys.dm_db_partition_stats ddps
        JOIN sys.indexes i ON i.object_id = ddps.object_id
                              AND i.index_id = ddps.index_id
WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
                              -- This is key (1 index per table)
        AND OBJECT_SCHEMA_NAME(ddps.object_id) &lt;> 'sys'
GROUP BY ddps.object_id ,
        i.type_desc ,
        i.Name
ORDER BY SchemaName ,
        TableName;

GO

Finding heaps (tables without clustered indexes)
Working with heaps is like working with a flat file instead of a database. If you want a guaranteed full table scan on every query, use heaps. It’s recommended to add a primary key to every heap table.
Method 1:

Select  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS HeapTable ,
        t.Create_Date
FROM    sys.tables t
        INNER JOIN sys.indexes i ON t.object_id = i.object_id
                                    AND i.type_desc = 'HEAP'
ORDER BY t.Name

Method 2:

Select  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        t.Name AS HeapTable ,
        t.Create_Date
FROM    sys.tables t
WHERE    OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0
ORDER BY t.Name;

Method 3 + row count:

Select  @@ServerName AS Server ,
        DB_NAME() AS DBName ,
        OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
        OBJECT_NAME(ddps.object_id) AS TableName ,
        i.Type_Desc ,
        SUM(ddps.row_count) AS Rows
FROM    sys.dm_db_partition_stats AS ddps
        JOIN sys.indexes i ON i.object_id = ddps.object_id
                              AND i.index_id = ddps.index_id
WHERE   i.type_desc = 'HEAP'
        AND OBJECT_SCHEMA_NAME(ddps.object_id) &lt;> 'sys'
GROUP BY ddps.object_id ,
        i.type_desc
ORDER BY TableName;

Figuring out table activity

When doing performance optimization work, it’s very important to know which tables get read heavily and which get written to heavily. Earlier we found out the row counts of our tables, now let’s see how often they get written to and read from.

Table reads/writes:

Heaps aren’t considered, they have no indexes
Only tables that were accessed since SQL Server started are processed

Select  @@ServerName AS ServerName ,
        DB_NAME() AS DBName ,
        OBJECT_NAME(ddius.object_id) AS TableName ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
                                                               AS  Reads ,
        SUM(ddius.user_updates) AS Writes ,
        SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
            + ddius.user_updates) AS [Reads&Writes] ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) / 86400.0
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleDays ,
        ( SELECT    DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
          FROM      master.sys.databases
          WHERE     name = 'tempdb'
        ) AS SampleSeconds
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
                                     AND i.index_id = ddius.index_id
WHERE    OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;

GO

A much more advanced version of this query is presented as a cursor, collecting info across all tables of all databases on the server.

Read and write operations
Heaps are skipped, they have no indexes

Only tables used since SQL Server restarted
The query uses a cursor to get info across all DBs
A single report, stored in tempdb

DECLARE DBNameCursor CURSOR
FOR
    SELECT  Name
    FROM    sys.databases
    WHERE    Name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                            'distribution' )
    ORDER BY Name;

DECLARE @DBName NVARCHAR(128)

DECLARE @cmd VARCHAR(4000)

IF OBJECT_ID(N'tempdb..TempResults') IS NOT NULL
    BEGIN
        DROP TABLE tempdb..TempResults
    END

CREATE TABLE tempdb..TempResults
    (
      ServerName NVARCHAR(128) ,
      DBName NVARCHAR(128) ,
      TableName NVARCHAR(128) ,
      Reads INT ,
      Writes INT ,
      ReadsWrites INT ,
      SampleDays DECIMAL(18, 8) ,
      SampleSeconds INT
    )

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor INTO @DBName
WHILE @@fetch_status = 0
    BEGIN

----------------------------------------------------
-- Print @DBName

        SELECT   @cmd = 'Use ' + @DBName + '; '
        SELECT   @cmd = @cmd + ' Insert Into tempdb..TempResults
SELECT @@ServerName AS ServerName,
DB_NAME() AS DBName,
object_name(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups) AS Reads,
SUM(ddius.user_updates) as Writes,
SUM(ddius.user_seeks
+ ddius.user_scans
+ ddius.user_lookups
+ ddius.user_updates) as ReadsWrites,
(SELECT datediff(s,create_date, GETDATE()) / 86400.0
FROM sys.databases WHERE name = ''tempdb'') AS SampleDays,
(SELECT datediff(s,create_date, GETDATE())
FROM sys.databases WHERE name = ''tempdb'') as SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i
ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE objectproperty(ddius.object_id,''IsUserTable'') = 1 --True
AND ddius.database_id = db_id()
GROUP BY object_name(ddius.object_id)
ORDER BY ReadsWrites DESC;'

--PRINT @cmd
        EXECUTE (@cmd)

-----------------------------------------------------

        FETCH NEXT FROM DBNameCursor INTO @DBName
    END

CLOSE DBNameCursor

DEALLOCATE DBNameCursor

SELECT  *
FROM    tempdb..TempResults
ORDER BY DBName ,
        TableName;
--DROP TABLE tempdb..TempResults;

Note: the cursor won’t work if you have databases in the list with a state other than ONLINE.

Views

Views are, loosely speaking, queries stored in the DB. You can think of them as virtual tables. Data isn’t stored in views, but in our queries we reference them exactly the same way as tables.

Select  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS ViewName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'V' -- View
ORDER BY o.NAME  

--OR

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        Name AS ViewName ,
        create_date
FROM    sys.Views
ORDER BY Name

--OR

SELECT  @@Servername AS ServerName ,
        TABLE_CATALOG ,
        TABLE_SCHEMA ,
        TABLE_NAME ,
        TABLE_TYPE
FROM     INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME

--OR

-- CREATE VIEW Code
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'ViewName' ,
        o.Type ,
        o.create_date ,
        sm.[DEFINITION] AS 'View script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.Type = 'V' -- View
ORDER BY o.NAME;

GO

Synonyms

Synonyms are rare, but figuring them out can cause certain difficulties if you’re not ready for them.

Select  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS ViewName ,
        o.Type ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'SN' -- Synonym
ORDER BY o.NAME;

--OR
-- additional info about synonyms

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        s.name AS synonyms ,
        s.create_date ,
        s.base_object_name
FROM    sys.synonyms s
ORDER BY s.name;

GO

Stored procedures

(Stored Procedures)
Stored procedures are a group of scripts that compile into a single execution plan.

-- Stored procedures
SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        o.name AS StoredProcedureName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.[Type] = 'P' -- Stored Procedures
ORDER BY o.name

--OR
-- Additional info about stored procedures

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'ViewName' ,
        o.[type] ,
        o.Create_date ,
        sm.[definition] AS 'Stored Procedure script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE   o.[type] = 'P' -- Stored Procedures
        -- AND sm.[definition] LIKE '%insert%'
        -- AND sm.[definition] LIKE '%update%'
        -- AND sm.[definition] LIKE '%delete%'
        -- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.name;

GO

By adding a simple condition to the WHERE clause we can get info only about those stored procedures that, for example, perform INSERT operations.

WHERE   o.[type]  = 'P' -- Stored Procedures
        AND sm.definition LIKE '%insert%'
ORDER BY o.name
…

By slightly modifying the WHERE condition, you can gather info about stored procedures that perform updates, deletes, or reference specific tables.

Functions

Functions are stored in SQL Server, take some parameters, and perform certain actions or computations, then return a result.

-- Functions

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'Functions' ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
WHERE   o.Type = 'FN' -- Function
ORDER BY o.NAME;

--OR
-- Additional info about functions

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS 'FunctionName' ,
        o.[type] ,
        o.create_date ,
        sm.[DEFINITION] AS 'Function script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.[Type] = 'FN' -- Function
ORDER BY o.NAME;

GO

Triggers

A trigger is something like a stored procedure that runs in response to certain actions on the table it belongs to.

Select  @@Servername AS ServerName ,
        DB_NAME() AS DBName ,
        parent.name AS TableName ,
        o.name AS TriggerName ,
        o.[Type] ,
        o.create_date
FROM    sys.objects o
        INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE   o.Type = 'TR' -- Triggers
ORDER BY parent.name ,
        o.NAME

--OR

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        Parent_id ,
        name AS TriggerName ,
        create_date
FROM    sys.triggers
WHERE   parent_class = 1
ORDER BY name;

--OR
-- Additional info about triggers

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        OBJECT_NAME(Parent_object_id) AS TableName ,
        o.name AS 'TriggerName' ,
        o.Type ,
        o.create_date ,
        sm.[DEFINITION] AS 'Trigger script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE   o.Type = 'TR' -- Triggers
ORDER BY o.NAME;

GO

Twitter Facebook LinkedIn