SQL Server tips

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;