Find Tables by Column Name
select sys.objects.name, sys.columns.name
from sys.columns inner join sys.objects on sys.columns.object_id = sys.objects.object_id
where sys.columns.name like '%criteria%' and sys.objects.type = 'U'
order by sys.objects.name
Find Table/Column by Value
DECLARE @VALUE_TO_SEARCH VARCHAR(MAX) = 'RR0303.07';
DECLARE @COLUMNS_TO_SEARCH TABLE ( ID INT IDENTITY, SCHEMA_NAME VARCHAR(25), TABLE_NAME VARCHAR(100), COLUMN_NAME VARCHAR(100) );
INSERT INTO @COLUMNS_TO_SEARCH ( SCHEMA_NAME, TABLE_NAME, COLUMN_NAME )
SELECT s.name, t.name, c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.system_type_id IN ( SELECT system_type_id FROM sys.types WHERE name IN ( 'varchar', 'nvarchar' ) )
ORDER BY s.name, t.name, c.name;
DECLARE @i INT = 1;
DECLARE @COLUMN_COUNT INT = ( SELECT MAX(ID) FROM @COLUMNS_TO_SEARCH );
DECLARE @SCHEMA_NAME VARCHAR(25);
DECLARE @TABLE_NAME VARCHAR(100);
DECLARE @COLUMN_NAME VARCHAR(100);
DECLARE @SQL NVARCHAR(MAX);
WHILE @i <= @COLUMN_COUNT BEGIN
SELECT @SCHEMA_NAME = SCHEMA_NAME,
@TABLE_NAME = TABLE_NAME,
@COLUMN_NAME = COLUMN_NAME
FROM @COLUMNS_TO_SEARCH
WHERE ID = @i;
SET @SQL = 'IF EXISTS ( SELECT * FROM [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] WHERE [' + @COLUMN_NAME + '] = ''' + @VALUE_TO_SEARCH + ''' ) BEGIN PRINT (''' + @SCHEMA_NAME + '.' + @TABLE_NAME + '.' + @COLUMN_NAME + ''') END';
EXEC sp_executesql @SQL;
SET @i += 1;
END
Currently Running Queries or Processes
select r.session_id, t.[text], r.[status], r.command, r.cpu_time, r.total_elapsed_time
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) t
select p.spid, right(convert(varchar, dateadd(ms, datediff(ms, p.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration', p.program_name, p.hostname, p.loginame
from master.dbo.sysprocesses P
where p.spid > 50 and p.[status] not in ('background', 'sleeping')
and p.cmd not in ('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')
order by batch_duration desc
Find Row Count for all Tables
create table #tableCounts ( tableName varchar(500), recordCount int )
exec sp_msforeachtable @command1 = 'insert #tableCounts ( tableName, recordCount ) select ''?'', count(*) from ?'
select tableName, recordCount from #tableCounts where tableName not like '%_LU_%' order by recordCount desc
SQL Server Hardware
select cpu_count as 'Logical CPU Count', hyperthread_ratio as 'Hyperthread Ratio',
cpu_count/hyperthread_ratio as 'Physical CPU Count',
physical_memory_in_bytes/1048576 as 'Physical Memory (MB)', sqlserver_start_time
from sys.dm_os_sys_info
File Path for all Database Instances
select dbid, fileid, filename from sys.sysaltfiles
Recovery Model for all Database Instances
select [name], recovery_model_desc, log_reuse_wait_desc, [compatibility_level] from sys.databases
Log File Percent Used for all Database Instances
select instance_name as [Database], cast(cntr_value as varchar(3)) + '%' as Log_File_Percent_Used
from sys.dm_os_performance_counters
where counter_name LIKE 'Percent Log Used%' and instance_name not in ('_Total', 'mssqlsystemresource')
Log File Pieces
dbcc loginfo('DATABASE_NAME')
File Size and Space Available for Current Database
select name as 'File Name', physical_name as 'Physical Name', size/128 as 'Total Size in MB',
size/128.0 - cast(fileproperty(name, 'SpaceUsed') as int)/128.0 as 'Available Space In MB'
from sys.database_files
Check Database Properties (example AnsiNulls & Collation)
select databasepropertyex('DatabaseName', 'IsAnsiNullsEnabled');
select databasepropertyex('DatabaseName', 'collation');
Cached Stored Procedures by Execution Count
select top 50 p.name as 'SP Name', qs.execution_count,
isnull(qs.execution_count/datediff(second, qs.cached_time, getdate()), 0) as 'Calls/Second',
qs.total_worker_time/qs.execution_count as 'AvgWorkerTime', qs.total_worker_time as 'TotalWorkerTime',
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count as 'avg_elapsed_time', qs.last_elapsed_time,
qs.cached_time, qs.last_execution_time
from sys.procedures as p
inner join sys.dm_exec_procedure_stats as qs
on p.object_id = qs.object_id
order by qs.execution_count desc
Cached Execution Plans
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
Find Tables With Foreign Keys
select cast(f.name as varchar(255)) as foreign_key_name,
cast(c.name as varchar(255)) as foreign_table,
cast(fc.name as varchar(255)) as foreign_column,
cast(p.name as varchar(255)) as parent_table,
cast(rc.name as varchar(255)) as parent_column
from sysobjects f inner join
sysobjects c on f.parent_obj = c.id inner join
sysreferences r on f.id = r.constid inner join
sysobjects p on r.rkeyid = p.id inner join
syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid inner join
syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
where f.type = 'F'
Check create and modified date for Stored Procedure (P) or Function (FN)
select name, create_date, modify_date from sys.objects where type = 'P'
Create hashed string
select substring(master.dbo.fn_varbintohexstr(hashbytes('sha1', 'Hello World')), 3, 40)
Check Index Fragmentation
select object_name(ind.OBJECT_ID) as TableName,
ind.name as IndexName,
indexstats.index_type_desc as IndexType,
indexstats.avg_fragmentation_in_percent as FragmentationPercent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) indexstats
inner join sys.indexes ind on ind.object_id = indexstats.object_id
and ind.index_id = indexstats.index_id
where indexstats.avg_fragmentation_in_percent > 30
order by indexstats.avg_fragmentation_in_percent desc
Missing Indexes By Index Advantage
select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.last_user_seek,
mid.[statement] AS [Database.Schema.Table],
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.unique_compiles,
migs.user_seeks,
migs.avg_total_user_cost,
migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs WITH (NOLOCK)
inner join sys.dm_db_missing_index_groups as mig WITH (NOLOCK) on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid WITH (NOLOCK) on mig.index_handle = mid.index_handle
order by index_advantage desc
Possible Bad Indexes
select object_name(s.object_id) as TableName,
i.name as IndexName,
i.index_id as IndexID,
user_updates as TotalWrites,
user_seeks + user_scans + user_lookups as TotalReads,
user_updates - (user_seeks + user_scans + user_lookups) as Difference
from sys.dm_db_index_usage_stats as s with (nolock)
inner join sys.indexes as i with (nolock) on s.object_id = i.object_id
and i.index_id = s.index_id
where objectproperty(s.object_id,'IsUserTable') = 1
and s.database_id = db_id()
and user_updates > (user_seeks + user_scans + user_lookups)
and i.index_id > 1
order by Difference desc,
TotalWrites desc,
TotalReads asc;
Blocking
Get Currently Blocked Sessions
select er.session_id,
host_name,
program_name,
original_login_name,
er.reads,
er.writes,
er.cpu_time,
wait_type,
wait_time,
wait_resource,
blocking_session_id,
st.text
from sys.dm_exec_sessions es
left join sys.dm_exec_requests er on es.session_id = er.session_id
outer apply sys.dm_exec_text(er.sql_handle) st
where blocking_session_id > 0
union
select es.session_id,
host_name,
program_name,
original_login_name,
es.reads,
es.writes,
es.cpu_time,
wait_type,
wait_time,
wait_resource,
blocking_session_id,
st.text
from sys.dm_exec_sessions es
left join sys.dm_exec_requests er on es.session_id = er.session_id
outer apply sys.dm_exec_text(er.sql_handle) st
where es.session_id in ( select blocking_session_id
from sys.dm_exec_requests
where blocking_session_id > 0 )
Before SQL Server 2005
select spid,
status,
blocked,
open_tran,
waitresource,
waittype,
waittime,
cmd,
lastwaittype
from master.dbo.sysprocesses
where blocked <> 0
Get First 4,000 Characters of Session by SPID
dbcc inputbuffer({spid})
Get Session Locks by SPID
exec sp_lock {spid}
Get Total Waiting Tasks/Wait Times Since Instance Started
dbcc sqlperf(waitstats)
Check Primary Key Exists on Table
if exists ( select * from sys.objects where object_id = object_id('PRIMARYKEYNAME') and type = 'PK' and parent_object_id = object_id('TABLENAME') )
Estimated Time Remaining for Database Restore
select r.percent_complete as percentComplete,
r.start_time as startTime,
r.command,
datediff(minute, r.start_time, getdate()) as executingTime,
r.estimated_completion_time/60000 as estimatedMinutesRemaining,
dateadd(ms, r.estimated_completion_time, getdate()) as estimatedCompletionTime
from sys.dm_exec_requests r
inner join sys.databases d on r.database_id = d.database_id
where r.command = 'RESTORE DATABASE'
Restore Database
restore filelistonly from disk = 'PATHTOBACKUPFILE.bak'
restore database DATABASENAME from disk = 'PATHTOBACKUPFILE.bak' with move 'MDFNAME' to 'M:\PATHTOMDF.mdf', move 'LDFNAME' to 'L:\PATHTOLDF.ldf'
Generate "Create Synonym" Scripts for all Table in a Database
select 'create synonym ' + t.name + ' for [' + db_name() + '].[' + s.name + '].[' + t.name + ']'
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U'
order by t.name