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>';
Top comments (0)