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

From Jens' Hjørne
Jump to navigation Jump to search
m
m
 
(9 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 '%profile_info%'
+
  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.
  
−
These are the possible object types as of SQL Server 2008 R2:
+
==Finding columns and their data types for a specific table==
−
{|
+
 
−
!type
+
Use this snippet to check if MSSMS have updated the column list for a table correctly.
−
!type_desc
+
 
−
|-
+
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
−
|C||CHECK_CONSTRAINT
+
from sys.objects obj
−
|-
+
  inner join sys.schemas as sch
−
|D||DEFAULT_CONSTRAINT
+
    on obj.schema_id = sch.schema_id
−
|-
+
  inner join sys.columns as c
−
|F||FOREIGN_KEY_CONSTRAINT
+
    on c.object_id = obj.object_id
−
|-
+
  inner join sys.types as t
−
|FN||SQL_SCALAR_FUNCTION
+
    on t.user_type_id = c.user_type_id
−
|-
+
where sch.name = 'SCHEMA NAME'
−
|IF||SQL_INLINE_TABLE_VALUED_FUNCTION
+
  and obj.name = 'TABLE NAME'
−
|-
+
order by c.column_id
−
|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==
 
==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 = 'other_fsc_count'
+
  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 '%usp_csc_get_recent_changes%'
+
   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