Displays the column information of all tables in SQL SERVER.

Displays the column information of all tables in SQL SERVER.

I am working on a program for importing an Excel file from the SQL server database. I want to read the column information in the database, find a lot of information from the Internet, and finally summarize 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:

 

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 primary key = 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

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