Top common SQL questions in most of the interviews.

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.

2. Find number of Tables in Database.

3. Find number of Stored Procedures in Database.

4. Retrieve list of Tables with its row count.

5. Find Created and Modified Date for Tables.

6. Find Created and Modified Date for Stored Procedures.

7. Find list of Tables containing a Column Name.

8. Find list of Objects containing a Text.

9. Find physical location of Database file.

10. Find the login with admin level roles.

11. Find last Database backup date.

12. Find most intensively read queries in Database.

13. Find current users, blocked process, memory, etc.

14. Find list of index for Tables.

15. Find Database size and Log file size.

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

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

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