Difference between revisions of "Using the T-SQL System Tables"

From Jens' Hjørne
Jump to navigation Jump to search
m
m
Line 12: Line 12:
 
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.
 
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:
+
==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
 +
 
 +
These are the possible object types in sys-objects as of SQL Server 2008 R2:
 
{|
 
{|
 
!style="text-align:left;"|type
 
!style="text-align:left;"|type
Line 48: Line 73:
 
|V||||VIEW
 
|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
 
  
 
[[Category:SQL]]
 
[[Category:SQL]]

Revision as of 15:18, 15 April 2015

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

These are the possible object types in sys-objects 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