T-SQL Bites

Check if a table with the specified name exists or not.

select obj.*
from sys.objects as obj
join sys.schemas as sch on sch.schema_id = obj.schema_id
where OBJECTPROPERTY(obj.object_id, 'IsTable') = 1 -- obj.type = 'U'
and sch.name = '<schema_name>'
and obj.name = '<table_name>'

You can use the OBJECTPROPERTY function to resolve other type of objects in the database.

Check if a foreign key between two tables with a specified name exists or not.

select fk.* from sys.foreign_keys fk
join sys.schemas as sch on sch.schema_id = fk.schema_id
join sys.objects as parentobj on parentobj.object_id = fk.parent_object_id and parentobj.schema_id = sch.schema_id
join sys.objects as refobj on refobj.object_id = fk.referenced_object_id and refobj.schema_id = sch.schema_id
where sch.name = '<schema_name>'
and fk.name = '<foreign_key_name>'
and parentobj.name = '<foreign_key_table_name>'
and refobj.name = '<referenced_table_name>'

Check if an index with a specified name exists on a table or not.

select *
from sys.indexes as idx
join sys.objects as obj on obj.object_id = idx.object_id
join sys.schemas as sch on sch.schema_id = obj.schema_id
where idx.name = '<index_name>'
and obj.name = '<table_name>'
and sch.name = '<schema_name>'
-- and idx.is_primary_key = 1
-- and idx.is_unique_constraint = 1

 

Advertisement