Recently, I was working on a project which involved SQL Server. I made a note of some useful commands that help me find useful things out. Here they are:
Check column types for a specific table:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TOSENV'
Check column constraints
SELECT * FROM CONSTRAINT_COLUMN_USAGE
Check length of text in a varchar column
SELECT LEN(MYCOLUMN) FROM MYTABLE;
List all tables that have a column named EntityID
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EntityID'
Check all constrains...
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Find blocked processes
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0
To see long running open transactions
DBCC OPENTRAN;
Check the lock timeout
SELECT @@LOCK_TIMEOUT
Check current session
SSP_WHO2
Find creation date of databases
SELECT NAME, CRDATE FROM MASTER..SYSDATABASES
Get the database id for a database.
SELECT DB_ID('PLANES')
Get the median value for a column named responsetime in a table allrequests
SELECT( (SELECT MAX(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime) AS BottomHalf) + (SELECT MIN(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime DESC) AS TopHalf)) / 2 AS Median
Most CPU intensive queries
SELECT HIGHEST_CPU_QUERIES.PLAN_HANDLE, HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME, Q.DBID, Q.OBJECTID, Q.NUMBER, Q.ENCRYPTED, Q.[TEXT]FROM (SELECT TOP 50 QS.PLAN_HANDLE, QS.TOTAL_WORKER_TIME FROM SYS.DM_EXEC_QUERY_STATS QS ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS QORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC INDEXES AND KEYS
Get me all the tables that have a FK to the table named Person
SELECT OBJECT_NAME ([PARENT_OBJECT_ID]) AS 'REFERENCING TABLE', * FROM SYS.FOREIGN_KEY_COLUMNSWHERE [REFERENCED_OBJECT_ID] = OBJECT_ID ('PERSON'));
Check all objects that have changed in last ten days
SELECT NAME AS OBJECT_NAME ,SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME ,TYPE_DESC ,CREATE_DATE ,MODIFY_DATEFROM SYS.OBJECTS WHERE MODIFY_DATE > GETDATE() - 10 ORDER BY MODIFY_DATE;
Disable indexes
ALTER INDEX [IX_NAME] ON TABLE DISABLE
Enable indexes
ALTER INDEX [IX_NAME] ON TABLE REBUILD USERS CHECK CURRENT USER
Check current user
SELECT SYSTEM_USER;
Check database owner
SELECT NAME, SUSER_SNAME(OWNER_SID) FROM SYS.DATABASES STATS
Check last time Stats was run
SELECT NAME AS STATS_NAME, STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE FROM SYS.STATSWHERE OBJECT_ID = OBJECT_ID('DBO.TASCONTACT');
Run stats for a table named Person in Sales
UPDATE STATISTICS SALES.PERSON
Check the size of various tables
SELECT OBJECT_NAME(P.OBJECT_ID) AS "TABLE", P.INDEX_ID, F.NAME, SUM(TOTAL_PAGES)/128 AS "SIZE IN MB", CONVERT(VARCHAR(10), GETDATE(), 101), COUNT(*) AS PARTITIONSFROM SYS.PARTITIONS P JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID JOIN SYS.FILEGROUPS F ON A.DATA_SPACE_ID = F.DATA_SPACE_IDGROUP BY P.OBJECT_ID, P.INDEX_ID, F.NAMEORDER BY SUM(TOTAL_PAGES)/128 DESC
Find the creation time for all tables.
SELECT NAME AS TABLENAME, CREATE_DATE AS CREATEDDATE FROM SYS.TABLES VERSION
Check version
SELECT @@VERSION;
or for more info
SELECT SERVERPROPERTY('EDITION') AS SQL_Server_Edtition, SERVERPROPERTY('PRODUCTLEVEL') AS SQL_Product_Level, SERVERPROPERTY('PRODUCTVERSION') AS SQL_Version;
Check column types for a specific table:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='TOSENV'
Check column constraints
SELECT * FROM CONSTRAINT_COLUMN_USAGE
Check length of text in a varchar column
SELECT LEN(MYCOLUMN) FROM MYTABLE;
List all tables that have a column named EntityID
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'EntityID'
Check all constrains...
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Find blocked processes
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED > 0
To see long running open transactions
DBCC OPENTRAN;
Check the lock timeout
SELECT @@LOCK_TIMEOUT
Check current session
SSP_WHO2
Find creation date of databases
SELECT NAME, CRDATE FROM MASTER..SYSDATABASES
Get the database id for a database.
SELECT DB_ID('PLANES')
Get the median value for a column named responsetime in a table allrequests
SELECT( (SELECT MAX(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime) AS BottomHalf) + (SELECT MIN(responsetime) FROM (SELECT TOP 50 PERCENT responsetime FROM allrequests ORDER BY responsetime DESC) AS TopHalf)) / 2 AS Median
Most CPU intensive queries
SELECT HIGHEST_CPU_QUERIES.PLAN_HANDLE, HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME, Q.DBID, Q.OBJECTID, Q.NUMBER, Q.ENCRYPTED, Q.[TEXT]FROM (SELECT TOP 50 QS.PLAN_HANDLE, QS.TOTAL_WORKER_TIME FROM SYS.DM_EXEC_QUERY_STATS QS ORDER BY QS.TOTAL_WORKER_TIME DESC) AS HIGHEST_CPU_QUERIES CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS QORDER BY HIGHEST_CPU_QUERIES.TOTAL_WORKER_TIME DESC INDEXES AND KEYS
Get me all the tables that have a FK to the table named Person
SELECT OBJECT_NAME ([PARENT_OBJECT_ID]) AS 'REFERENCING TABLE', * FROM SYS.FOREIGN_KEY_COLUMNSWHERE [REFERENCED_OBJECT_ID] = OBJECT_ID ('PERSON'));
Check all objects that have changed in last ten days
SELECT NAME AS OBJECT_NAME ,SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME ,TYPE_DESC ,CREATE_DATE ,MODIFY_DATEFROM SYS.OBJECTS WHERE MODIFY_DATE > GETDATE() - 10 ORDER BY MODIFY_DATE;
Disable indexes
ALTER INDEX [IX_NAME] ON TABLE DISABLE
Enable indexes
ALTER INDEX [IX_NAME] ON TABLE REBUILD USERS CHECK CURRENT USER
Check current user
SELECT SYSTEM_USER;
Check database owner
SELECT NAME, SUSER_SNAME(OWNER_SID) FROM SYS.DATABASES STATS
Check last time Stats was run
SELECT NAME AS STATS_NAME, STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE FROM SYS.STATSWHERE OBJECT_ID = OBJECT_ID('DBO.TASCONTACT');
Run stats for a table named Person in Sales
UPDATE STATISTICS SALES.PERSON
Check the size of various tables
SELECT OBJECT_NAME(P.OBJECT_ID) AS "TABLE", P.INDEX_ID, F.NAME, SUM(TOTAL_PAGES)/128 AS "SIZE IN MB", CONVERT(VARCHAR(10), GETDATE(), 101), COUNT(*) AS PARTITIONSFROM SYS.PARTITIONS P JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID JOIN SYS.FILEGROUPS F ON A.DATA_SPACE_ID = F.DATA_SPACE_IDGROUP BY P.OBJECT_ID, P.INDEX_ID, F.NAMEORDER BY SUM(TOTAL_PAGES)/128 DESC
Find the creation time for all tables.
SELECT NAME AS TABLENAME, CREATE_DATE AS CREATEDDATE FROM SYS.TABLES VERSION
Check version
SELECT @@VERSION;
or for more info
SELECT SERVERPROPERTY('EDITION') AS SQL_Server_Edtition, SERVERPROPERTY('PRODUCTLEVEL') AS SQL_Product_Level, SERVERPROPERTY('PRODUCTVERSION') AS SQL_Version;
No comments:
Post a Comment