Using the T-SQL System Tables
Jump to navigation
Jump to search
Finding schema and object from a partial object name
We often don't use the full table/view name, when we speak about a data object. This snippet will help new developers in finding what the real name of the object is.
select sch.name as SchemaName, obj.Name as TableName, obj.type, obj.type_desc
from sys.objects obj
inner join sys.schemas as sch
on obj.schema_id = sch.schema_id
where obj.name like '%profile_info%'
and obj.type in ('U', 'V', 'P', 'FN') -- U=User table, V=View, P=Stored procedure, FN=Scalar function
order by obj.Name
The last line in the where-clause (and obj.type in ('U', 'V', 'P', 'FN')) will avoid the query listing things like constraints, internal tables, system tables and service queues.
Finding the schema and table/view from a column name
This snippet is useful, if you are unsure where a specific column comes from.
select sch.name as SchemaName, obj.Name as TableName, col.Name as ColumnName
from sys.columns col
inner join sys.objects obj
on obj.object_id = col.object_id
inner join sys.schemas as sch
on obj.schema_id = sch.schema_id
where col.name = 'other_fsc_count'
order by obj.Name, col.Name
Finding a stored procedure based on any text from the definition
select sch.name, obj.name, sm.definition
from sys.objects as obj
inner join sys.sql_modules as sm
on obj.object_id = sm.object_id
inner join sys.schemas as sch
on obj.schema_id = sch.schema_id
where type='P' and
definition like '%usp_csc_get_recent_changes%'
order by obj.name