DEV Community

loading...

Useful SQL Server sys table queries and stored procs

mani-playground
I jot down random tech stuff here. It's great if it helps others too but the main intent is for me to come back later and refer to it, if ever. So don't expect the posts to be detailed or organized.
Updated on ・1 min read

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)