Displays the column information of all or one table in SQL Server.

I am working on a program for the SQL Server database. I want to read the column information in the database, find a lot of information from the Internet, and finally find out the ideal SQL statement, after execution, the returned columns are: Table Name, column name, column type, column length, column description, and whether the primary key is used. The statement is as follows:

(If you only want to operate a table, replace the last % with the table name .)

Sqlserver2000 can be used:

 

Select sysobjects. Name as tb_name, syscolumns. Name as col_name, policypes. Name as col_type, syscolumns. length as col_len, isnull (sysproperties. Value, syscolumns. Name) as col_memo,
Case when syscolumns. Name in
(Select primarykey = A. Name
From syscolumns
Inner join sysobjects B on A. ID = B. ID and B. xtype = 'U' and B. Name <> 'dtproperties'
Where exists (select 1 from sysobjects where xtype = 'pk' and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = A. ID and colid = A. colid
)))
And B. Name = sysobjects. Name
)
Then 1 else 0 end as is_key
From sysobjects, policypes, syscolumns
Left join sysproperties on (syscolumns. ID = sysproperties. ID and
Syscolumns. colid = sysproperties. smallid)
Where (sysobjects. xtype = 'U' or sysobjects. xtype = 'V ')
And sysobjects. ID = syscolumns. ID and policypes. xtype = syscolumns. xtype
And policypes. Name <> 'sysname' and sysobjects. name like '%' order by sysobjects. Name, syscolumns. colid

Sqlserver2005 can be used:

Select sysobjects. Name as tb_name, syscolumns. Name as col_name, policypes. Name as col_type, syscolumns. length as col_len, isnull (SYS. extended_properties.value, syscolumns. Name) as col_memo,
Case when syscolumns. Name in
(Select primarykey = A. Name
From syscolumns
Inner join sysobjects B on A. ID = B. ID and B. xtype = 'U' and B. Name <> 'dtproperties'
Where exists (select 1 from sysobjects where xtype = 'pk' and name in (
Select name from sysindexes where indid in (
Select indid from sysindexkeys where id = A. ID and colid = A. colid
)))
And B. Name = sysobjects. Name
)
Then 1 else 0 end as is_key
From sysobjects, policypes, syscolumns
Left join SYS. extended_properties on (syscolumns. ID = SYS. extended_properties.major_id and
Syscolumns. colid = SYS. extended_properties.minor_id)
Where (sysobjects. xtype = 'U' or sysobjects. xtype = 'V ')
And sysobjects. ID = syscolumns. ID and policypes. xtype = syscolumns. xtype
And policypes. Name <> 'sysname' and sysobjects. name like '%' order by sysobjects. Name, syscolumns. colid