DEV Community

mani-playground
mani-playground

Posted on • Updated on

Useful SQL Server sys table queries and stored procs

List of tables in a schema with schema name

select s.name + '.' + o.name
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = '<schema name>'
and [type] = 'U';
Enter fullscreen mode Exit fullscreen mode

Create a new table with columns copied from an existing table (without data)

select top 0 * into NewTable 
from ExistingTable;
Enter fullscreen mode Exit fullscreen mode

Delete all tables in all schema in a database

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'
GO
Enter fullscreen mode Exit fullscreen mode

LIKE operation on an XML column
We can't do a like operation on an XML column in SQL Server. As a work around, just cast the column to varchar.

select *
from schema.table
where CAST(xml_col_nm as nvarchar(max)) like '%search_term%'
Enter fullscreen mode Exit fullscreen mode

Stats
Check when stats was last updated for tables in a given schema

SELECT OBJECT_NAME(st.object_id) AS [ObjectName]
      ,st.[name] AS [StatisticName]
      ,STATS_DATE(st.[object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats st
inner join sys.objects o on o.object_id = st.object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = '<schema name>';
Enter fullscreen mode Exit fullscreen mode

Discussion (0)