Login
Password

Forgot your password?

15 Sql Server Queries for Metadata

By Edited Jun 1, 2015 0 0

15 SQL Server Queries to Boost Productivity

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"

SQL_Server_2008_Query_HostName_Version

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

SQL_Server_2008_Query_Table_Row_Count

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

SQL_Server_2008_Query_Table_Created_Modified_Dates

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

SQL_Server_2008_Query_SP_Created_Modified_Dates
Microsoft SQL Server 2008 All-in-One Desk Reference For Dummies
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%'

SQL_Server_2008_Query_Table_Containing_Column

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%';

SQL_Server_2008_Query_Objects_Containing_Text

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

SQL_Server_2008_Query_DB_Location

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

SQL_Server_2008_Query_Find_Admin_Users

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.TOTAL_LOGICAL_READS, QS.TOTAL_ELAPSED_TIME,
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

SQL_Server_2008_Query_Intensively_Read_Queries

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.

SQL_Server_2008_Query_sp_who2

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%';

SQL_Server_2008_Query_Table_Indexes

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

SQL_Server_2008_Query_Table_Indexes
Microsoft SQL Server 2012 Unleashed
Amazon Price: $59.99 $35.23 Buy Now
(price as of Jun 1, 2015)
Advertisement
Advertisement

Comments

Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology