SQL Server: Sample Scripts

Article
1/17/2024

To get details of all default constraints of a database.

Below script will return default constraints for specific a column.

SELECT
 df.name 'Constraint Name' ,
 --df.definition,  -- this gives the default value    
 t.name 'Table Name',
 c.NAME 'Column Name',
 SCHEMA_NAME(t.schema_id) 'Schema_Name'
 FROM sys.default_constraints df
 INNER JOIN sys.tables t  ON  df.parent_object_id = t.object_id
 INNER JOIN sys.columns c  ON  df.parent_object_id = c.object_id AND  df.parent_column_id = c.column_id
 WHERE  c.NAME  = 'Column_Name'  -- Column Name

Below script will return default constraints for a specific table having specific columns.

SELECT
 df.name 'Constraint Name' ,
 --df.definition,  -- this gives the default value    
 t.name 'Table Name',
 c.NAME 'Column Name',
 SCHEMA_NAME(t.schema_id) 'Schema_Name'
 FROM sys.default_constraints df
 INNER JOIN sys.tables t  ON  df.parent_object_id = t.object_id
 INNER JOIN sys.columns c  ON  df.parent_object_id = c.object_id AND  df.parent_column_id = c.column_id
 WHERE t.name = 'Table_Name'   --- put the table name here
 c.NAME  = 'Column_Name'  -- Column Name

To get all the table names having specific a Column Name.

select TABLE_NAME, TABLE_CATALOG, table_schema, column_name from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%Column_Name%' 
Above script will return  View as  well with  the table  name, below script will not return  View. 
select TABLE_NAME, TABLE_CATALOG, table_schema, column_name  from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%Column_Name%' and Table_name Not  like 'VW_%'

To get all foreign Key Constraints in a database.

SELECT * from sys.foreign_keys

To get all Foreign Keys and create script to drop it from database

SELECT
 'alter table ' + quotename(schema_name(schema_id)) + '.' +
 quotename(object_name(parent_object_id)) +
 ' drop constraint '+quotename(name)
 from sys.foreign_keys

To get all the indexes in a database (Clustered and No Clustered), we can have conditions to filter specific constraints.

Note: It will exclude primary key and unique key constraints.

select  *
 from sys.indexes i
 inner join sys.objects o  on   i.object_id=o.object_id
 inner join sys.schemas s  on  o.schema_id = s.schema_id
 where o.type<>'S'  and is_primary_key<>1 and index_id>0
 and s.name!='sys' and s.name!='sys' and is_unique_constraint=0

Below script will get all constraints along with a create script to drop all constraints, we can have conditions to filter specific constraints.

declare @qry nvarchar(max);
select @qry = (
 select  'IF EXISTS(SELECT * FROM sys.indexes WHERE name='''+ i.name +''' AND object_id = OBJECT_ID(''['+s.name+'].['+o.name+']''))  drop index ['+i.name+'] ON ['+s.name+'].['+o.name+'];  '
 from sys.indexes i
 inner join sys.objects o  on   i.object_id=o.object_id
 inner join sys.schemas s  on  o.schema_id = s.schema_id
 where o.type<>'S'  and is_primary_key<>1 and index_id>0
 and s.name!='sys' and s.name!='sys' and is_unique_constraint=0
for xml path(''));
exec sp_executesql @qry

To get all constraints along with Unique and primary Constraints.

select  *
 from sys.indexes i
 inner join sys.objects o  on   i.object_id=o.object_id
 inner join sys.schemas s  on  o.schema_id = s.schema_id
 where o.type<>'S'  and is_primary_key<>1 and index_id>0
 and s.name!='sys' and s.name!='sys' 
Query to  create script to delete  all constraint.
select @qry = (
 select  'IF EXISTS(SELECT * FROM sys.indexes WHERE name='''+ i.name +''' AND object_id = OBJECT_ID(''['+s.name+'].['+o.name+']''))  alter table ['+s.name+'].['+o.name+'] drop Constraint ['+i.name+'] ;  '
 from sys.indexes i
 inner join sys.objects o  on   i.object_id=o.object_id
 inner join sys.schemas s  on  o.schema_id = s.schema_id
 where o.type<>'S'   and index_id>0
 and s.name!='sys' and s.name!='sys' for  xml path(''));
print @qry
exec sp_executesql @qry

To get all the stored procedures, Triggers, Views and Functions that contain a specific column_name or any "Text".

SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition like '%Text%'

Share via