Listed below are the 15 basic sql queries that one must know to resolve issues or questions that arise at the metadata level. These queries will also help developers in doing analysis at the database level, such as finding the list of stored procedures that uses a particular table or finding the created or last modified date of a table or a stored procedure. I have tested all these queries against Microsoft SQL Server 2008 R2 and provided screenshots of the result set for your quick note. Let us go through the list now.
1. Find Host name, Edition and Version number
Useful query to retrieve host name, sql server edition and version details as shown in the screen shot below.
SELECT SERVERPROPERTY('MachineName') as Host, SERVERPROPERTY('InstanceName') as Instance, @@VERSION as "Version Number", SERVERPROPERTY('Edition') as Edition, SERVERPROPERTY('ProductLevel') as ProductLevel, Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 'STAND ALONE' end as "Server Type"
2. Find number of Tables in Database
The sysobjects table can be queried to get the count of different database objects like Tables, Stored Procedures, Views, Functions etc. by passing corresponding xtype values to the query.
SELECT COUNT(OBJ.ID) as "Table Count" FROM SYSOBJECTS OBJ WHERE XTYPE='U'
3. Find number of Stored Procedures in Database
SELECT COUNT(OBJ.ID) as "SP Count" FROM SYSOBJECTS OBJ WHERE XTYPE='P'
4. Retrieve list of Tables with its row count
SELECT OBJ.NAME as "Table Name", MAX(SI.ROWS) as "Row Count" FROM SYSOBJECTS OBJ, SYSINDEXES SI WHERE OBJ.XTYPE = 'U' AND SI.ID = OBJECT_ID(OBJ.NAME) GROUP BY OBJ.NAME ORDER BY 2 DESC
5. Find Created and Modified Date for Tables
The following 2 queries are extremely useful to find the recent changes to database objects by listing our the list of modified objects based on modified date.
SELECT NAME as "Table Name", CREATE_DATE as "Created Date",
MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS
WHERE TYPE = 'U' --AND NAME LIKE '%sys_%' ORDER BY 3 DESC
6. Find Created and Modified Date for Stored Procedures
SELECT NAME as "SP Name", CREATE_DATE as "Created Date",
MODIFY_DATE as "Modified Date" FROM SYS.OBJECTS
WHERE TYPE = 'P' --AND NAME LIKE '%delete%' ORDER BY 3 DESC
Amazon Price: $39.99 $21.27 Buy Now
(price as of Jun 1, 2015)
7. Find list of Tables containing a Column Name
This query gives a quick glance at the list of tables that has the same column names.
SELECT s.[NAME] 'Schema', t.[NAME] 'Table', c.[NAME] 'Column', d.[NAME] 'Data Type', d.[MAX_LENGTH] 'Max Length', c.[IS_IDENTITY] 'Is Id',
c.[IS_NULLABLE] 'Is Nullable', t.[MODIFY_DATE] 'Date Modified', t.[CREATE_DATE] 'Date created' FROM SYS.SCHEMAS s INNER JOIN SYS.TABLES t ON s.SCHEMA_ID= t.SCHEMA_ID INNER JOIN SYS.COLUMNS c ON t.OBJECT_ID= c.OBJECT_ID INNER JOIN SYS.TYPES d ON c.USER_TYPE_ID= d.USER_TYPE_ID WHERE c.NAME like '%backup%'
8. Find list of Objects containing a Text
This is the query that I use most for analysing issues in my production support project. This query comes as a gift to quickly find the database objects related to specific modules of the project by doing a wild card search using the proper keyword.
SELECT o.NAME FROM SYSCOMMENTS (nolock) AS c INNER JOIN SYSOBJECTS (nolock) AS o ON c.ID = o.id WHERE c.TEXT LIKE '%BACKUP%';
9. Find physical location of Database file
This query gives the exact location of mdf and ldf files of all the available database.
SELECT DB_NAME(DATABASE_ID) as DatabaseName, NAME, TYPE_DESC,
PHYSICAL_NAME FROM SYS.MASTER_FILES
10. Find the login with admin level roles
This query provides a snapshot of list of users having admin level roles in the database.
SELECT l.NAME, l.DENYLOGIN, l.ISNTNAME, l.ISNTGROUP, l.ISNTUSER
FROM MASTER.DBO.SYSLOGINS l WHERE l.SYSADMIN = 1 OR l.SECURITYADMIN = 1
11. Find last Database backup date
You can even schedule a job using the query below to send alerts if the database is not being backed up as per the scheduled plan.
SELECT db.NAME, CASE WHEN MAX(b.backup_finish_date) IS NULL THEN 'No Backup' ELSE convert(varchar(100), MAX(b.BACKUP_FINISH_DATE)) END AS last_backup_finish_date FROM SYS.DATABASES db LEFT OUTER JOIN MSDB.DBO.BACKUPSET b ON db.NAME = b.DATABASE_NAME AND b.TYPE = 'D' WHERE db.DATABASE_ID NOT IN (2) GROUP BY db.NAME ORDER BY 2 DESC
12. Find most intensively read queries in Database
This is the easy and the laziest way to find the list intensively read queries in the database. For performance optimization, this may give a lazy but quick solution.
SELECT TOP 50 SUBSTRING(QT.TEXT, (QS.STATEMENT_START_OFFSET/2) +1, ((CASE QS.STATEMENT_END_OFFSET WHEN -1 THEN (QT.TEXT) ELSE QS.STATEMENT_END_OFFSET
END - QS.STATEMENT_START_OFFSET)/2)+1), QS.EXECUTION_COUNT,
QS.LAST_ELAPSED_TIME, QS.MIN_ELAPSED_TIME, QS.MAX_ELAPSED_TIME, QS.LAST_EXECUTION_TIME, QP.QUERY_PLAN
FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(QS.PLAN_HANDLE) QP WHERE QT.ENCRYPTED=0 ORDER BY QS.TOTAL_LOGICAL_READS DESC, QS.TOTAL_ELAPSED_TIME DESC
13. Find current users, blocked process, memory, etc.
To find the list of processes and threads that are active in the database at the moment, just use 'sp_who2' to get the result set below. Use this query only when there is a need to look into the current threads and processes in database. This can be used in a situation where tables, jobs or objects are blocked or dead locked and when there is a need to find out which process is blocking the objects. The BlkBy column will contain the SPID of the process that blocks it.
14. Find list of index for Tables
SELECT A.NAME TABLE_NAME, B.NAME INDEX_NAME, D.NAME COLUMN_NAME FROM SYS.TABLES A, SYS.INDEXES B, SYS.INDEX_COLUMNS C, SYS.COLUMNS D WHERE A.OBJECT_ID = B.OBJECT_ID AND B.OBJECT_ID = C.OBJECT_ID AND B.INDEX_ID = C.INDEX_ID AND C.OBJECT_ID = D.OBJECT_ID AND C.COLUMN_ID = D.COLUMN_ID --AND A.NAME LIKE '%BACKUP%';
15. Find Database size and Log file size
WITH FS AS (SELECT DATABASE_ID, TYPE, SIZE * 8.0 / 1024 SIZE
FROM SYS.MASTER_FILES) SELECT NAME, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 0 AND FS.DATABASE_ID = DB.DATABASE_ID) DATAFILESIZEMB, (SELECT SUM(SIZE) FROM FS WHERE TYPE = 1 AND FS.DATABASE_ID = DB.DATABASE_ID) LOGFILESIZEMB FROM SYS.DATABASES DB