Some Useful SQL Queries
Here are some SQL queries that I like to use.
TO COUNT NUMBER OF TABLES IN A DB
SELECT [TABLECOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’U’
TO COUNT NUMBER OF STORED PROCEDURES IN A DB
SELECT [SPCOUNT] = COUNT(OBJ.ID) FROM SYSOBJECTS OBJ WHERE XTYPE=’P’
GET LIST OF TABLES NAMES AND THEIR ROW COUNTS
SELECT [TABLENAME] = OBJ.NAME, [ROWCOUNT] = MAX(SI.ROWS) FROM SYSOBJECTS OBJ, SYSINDEXES SI WHERE OBJ.XTYPE = ‘U’ AND SI.ID = OBJECT_ID(OBJ.NAME)
GROUP BY OBJ.NAME ORDER BY [ROWCOUNT] DESC
GET COLUMNS NAMES, DATA TYPES, IS NULLABLE OF A PARTICULAR TABLE
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE + CASE ISNULL(CHARACTER_MAXIMUM_LENGTH,”) WHEN ” THEN ” ELSE ‘ (‘ + CAST (CHARACTER_MAXIMUM_LENGTH AS VARCHAR(6)) + ‘)’ END AS [DATA TYPE], CASE IS_NULLABLE WHEN ‘YES’ THEN ‘NULL’ ELSE ‘NOT NULL’ END AS [NULL OR NOT] FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = ‘L_SAMPLE’ ORDER BY TABLE_NAME, COLUMN_NAME
TO SEE THE DEFINITION OF A SP
EXEC SP_HELPTEXT GET_USER_DATA
GET TABLES CREATION AND MODIFIED DATES
SELECT [TABLENAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS WHERE TYPE = ‘U’ –AND NAME LIKE ‘%L_%’
ORDER BY MODIFY_DATE DESC
GET STORED PROCEDURES CREATION AND MODIFIED DATES
SELECT [SPNAME] = NAME, CREATE_DATE, MODIFY_DATE FROM SYS.OBJECTS WHERE TYPE = ‘P’ –AND NAME LIKE ‘%GET%’
ORDER BY MODIFY_DATE DESC
Happy coding