Difference between revisions of "Using the T-SQL System Tables"
Jump to navigation
Jump to search
m |
m |
||
| (One intermediate revision by the same user not shown) | |||
| Line 6: | Line 6: | ||
inner join sys.schemas as sch | inner join sys.schemas as sch | ||
on obj.schema_id = sch.schema_id | on obj.schema_id = sch.schema_id | ||
| − | where obj.name like '% | + | where obj.name like '%PARTIAL NAME%' |
and obj.type in ('U', 'V', 'P', 'FN') -- U=User table, V=View, P=Stored procedure, FN=Scalar function | and obj.type in ('U', 'V', 'P', 'FN') -- U=User table, V=View, P=Stored procedure, FN=Scalar function | ||
order by obj.Name | order by obj.Name | ||
| Line 24: | Line 24: | ||
inner join sys.types as t | inner join sys.types as t | ||
on t.user_type_id = c.user_type_id | on t.user_type_id = c.user_type_id | ||
| − | where sch.name = ' | + | where sch.name = 'SCHEMA NAME' |
| − | and obj.name = ' | + | and obj.name = 'TABLE NAME' |
order by c.column_id | order by c.column_id | ||
| Line 38: | Line 38: | ||
inner join sys.schemas as sch | inner join sys.schemas as sch | ||
on obj.schema_id = sch.schema_id | on obj.schema_id = sch.schema_id | ||
| − | where col.name = ' | + | where col.name = 'COLUMN NAME' |
order by obj.Name, col.Name | order by obj.Name, col.Name | ||
| Line 50: | Line 50: | ||
on obj.schema_id = sch.schema_id | on obj.schema_id = sch.schema_id | ||
where type='P' and | where type='P' and | ||
| − | definition like '% | + | definition like '%TEXT FROM PROC%' |
order by obj.name | order by obj.name | ||
Latest revision as of 14:21, 24 May 2018
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 '%PARTIAL NAME%'
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 columns and their data types for a specific table
Use this snippet to check if MSSMS have updated the column list for a table correctly.
select sch.name as SchemaName, obj.Name as TableName, c.name, t.name, c.max_length, c.is_nullable, c.is_identity, c.is_rowguidcol, c.column_id
from sys.objects obj
inner join sys.schemas as sch
on obj.schema_id = sch.schema_id
inner join sys.columns as c
on c.object_id = obj.object_id
inner join sys.types as t
on t.user_type_id = c.user_type_id
where sch.name = 'SCHEMA NAME'
and obj.name = 'TABLE NAME'
order by c.column_id
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 = 'COLUMN NAME'
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 '%TEXT FROM PROC%'
order by obj.name
Additional information
You can get an overview of the possible object types in sys.objects by running this query:
select distinct obj.type, obj.type_desc from sys.objects obj order by obj.type
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 |