Sunday, October 21, 2007

Get schema of one table

select t2.name [TABLE_NAME]
, t1.name [COLUMN_NAME]
, t4.name [TYPE_NAME]
, t1.max_length
, t1.precision
, t1.scale
, t1.is_nullable
, t1.is_identity
, t3.seed_value
, t3.increment_value
from sys.columns t1
inner join sys.tables t2 on t1.[object_id] = t2.[object_id]
left join sys.identity_columns t3 on t1.column_id = t3.column_id and t2.[object_id] = t3.[object_id]
inner join sys.types t4 on t1.[user_type_id] = t4.[user_type_id]
where t2.name = 'table_name'

Friday, October 19, 2007

Access SQL Server catalog

Sometimes, you need to know schema in details of your database. You may use system objects such as sysobjects/ sys.objects, syscolumns/ sys.columns, etc or system stored procedures to query that information.
Another way to get that information is using INFORMATION SCHEMA in SQL Server 2005:

View Contains information about

COLUMNS

Columns accessible to the current user in the current database.

INDEXES

Indexes in the current database.

KEY_COLUMN_USAGE

Keys in the current database.

PROVIDER_TYPES

Data types supported in SQL Server Mobile.

TABLES

Tables accessible to the current user in the current database.

TABLE_CONSTRAINTS

Table constraints in the current database.

REFERENTIAL_CONSTRAINTS

Foreign constraint in the current database

Source: SQL Server Book online
For example:
If you want to know the schema of tblEmployee Table, you can query like that:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblEmployee'