Useful SQL Server sys table queries and stored procs

#database #sql #sqlserver

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';

List of tables which has the column

SELECT      
    TABLE_SCHEMA AS 'Schema',
    COLUMN_NAME AS 'ColumnName',
           TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%ColumnName%'
ORDER BY    TableName
            ,ColumnName;

Recently modified objects

select [type] OBJ_TYPE, s.name + '.' + o.name, *
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
where 1=1
and [type] IN ('P','IF', 'FN', 'TF', 'V')
and o.create_date > '2023-06-05 11:07:31.187' --replace with required date
and s.name in ('dbo') -- modify / add schemas to include
ORDER BY [type], s.name, o.create_date

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

select top 0 * into NewTable 
from ExistingTable;

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

Find objects (Stored proc, function etc.,) that contain the given text

SELECT DISTINCT
    o.name AS object_name,
    o.type_desc AS object_type,
    s.name
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON o.object_id = m.object_id
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE m.definition LIKE '%ReplaceWithTextToSearch%'

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%'

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>';