Interesting SQL Queries

• Query to find Procedures that contains a particular string in MS SQL 2005

select O.name from sysComments C join sysObjects O on O.id = C.id where O.xtype = 'P' and C.text like '%string%'

• Query to find all the user-defined tables in a specified database

USE db;
GO
SELECT *
FROM sys.tables;
GO

• Query to find find all the tables and indexes that are partitioned

USE db;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,OBJECT_NAME(p.object_id) AS table_name,i.name AS index_name,p.partition_number,rows
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO

• Query to find all the statistics on a specified object

USE db ;
GO
SELECT name AS statistics_name, stats_id,auto_created,user_created,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('');
GO

• Query to find all the statistics and statistics columns on a specified object

USE db;
GO
SELECT s.name AS statistics_name, c.name AS column_name, sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('');
GO