Difference between revisions of "Using the T-SQL System Tables"
Jump to navigation
Jump to search
m |
m |
||
| (8 intermediate revisions 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 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. | ||
| − | + | ==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== | ==Finding the schema and table/view from a column name== | ||
| Line 57: | 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 69: | 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 | ||
| + | |||
| + | ==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: | ||
| + | {| | ||
| + | !style="text-align:left;"|type | ||
| + | | | ||
| + | !style="text-align:left;"|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 | ||
| + | |} | ||
[[Category:SQL]] | [[Category:SQL]] | ||
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 |