You are working on a program for importing an Excel file from a SQL Server database. To read the columns in the database information, looking for a lot of information from the Internet, and finally summed up the more ideal SQL statements, after the execution of the returned columns are: Table name, column name, column type, column length, column description, whether the primary key, Statement as follows:1 Select sysobjects.name as Tb_name, syscolumns.name as Col_name, systypes.name as Col_type, syscolumns.length as Col_len, Isnull (Sysproperties.value,syscolumns.name) as Col_memo,
2 case when syscolumns.name in
3 (Select primary key =a.name
4 from Syscolumns A
5 Inner Join sysobjects B on a.id=b.id and b.xtype= ' U ' and b.name<> ' dtproperties '
6 where Exists (Select 1 from sysobjects Where xtype= ' Pk "and Name in (
7 Select Name from sysindexes Where indid in (
8 Select indid from Sysindexkeys Where Id = a.id and Colid=a.colid
9))
B.name=sysobjects.name
11)
Then 1 Else 0 End as Is_key
13
From Sysobjects,systypes,syscolumns
Left Join sysproperties on (syscolumns.id = Sysproperties.id and
Syscolumns.colid = sysproperties.smallid)
17
Where (sysobjects.xtype = ' U ' Or sysobjects.xtype = ' V ')
Sysobjects.id = syscolumns.id and Systypes.xtype = Syscolumns.xtype
Systypes.name <> ' Sysname ' and sysobjects.name like '% ' order by Sysobjects.name, Syscolumns.colid
The result is as shown in figure: