Difference between revisions of "Using the T-SQL System Tables"
Jump to navigation
Jump to search
m |
m |
||
| Line 24: | Line 24: | ||
where col.name = 'other_fsc_count' | where col.name = 'other_fsc_count' | ||
order by obj.Name, col.Name | order by obj.Name, col.Name | ||
| + | |||
| + | ==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 = 'Main' | ||
| + | and obj.name = 'T_filter' | ||
| + | order by c.column_id | ||
| + | |||
==Finding a stored procedure based on any text from the definition== | ==Finding a stored procedure based on any text from the definition== | ||
Revision as of 10:20, 22 July 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 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 = 'Main'
and obj.name = 'T_filter'
order by c.column_id
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
Additional information
You can get an overview of the possible object types in sys.objects by unning 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 |