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.
These are the possible object types as of SQL Server 2008 R2:
| type | type_desc |
|---|---|
| C | CHECK_CONSTRAINT |
| D | DEFAULT_CONSTRAINT |
| F | FOREIGN_KEY_CONSTRAINT |
| FN | SQL_SCALAR_FUNCTION |
| IF | SQL_INLINE_TABLE_VALUED_FUNCTION |
| IT | INTERNAL_TABLE |
| P | SQL_STORED_PROCEDURE |
| PK | PRIMARY_KEY_CONSTRAINT |
| R | RULE |
| S | SYSTEM_TABLE |
| SQ | SERVICE_QUEUE |
| TR | SQL_TRIGGER |
| U | USER_TABLE |
| UQ | UNIQUE_CONSTRAINT |
| V | VIEW |
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