讀取sql server表的欄位名，欄位類型，欄位長度，欄位屬性描述的sql語句

得到表的欄位名（排序）：

SELECT a.name as 欄位名 FROM syscolumns a inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' where d.name = '表名' order by a.id,a.colorder

得到表的欄位名：

Select name  from syscolumns Where ID=OBJECT_ID('表名')

 

可橫向獲欄位名：

select * from 表名
declare @str varchar(4000)
set @str=''
select @str=@str+','+ 欄位名 from 表名
select right(@str,len(@str)-1)
print @str

列印可獲STR長字元。

 

另得表的欄位名，欄位類型，欄位長度，欄位屬性：

SELECT a.name 欄位名,COLUMNPROPERTY(a.id,a.name,'IsIdentity') 標識,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT
colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) 主鍵,b.name 類型,COLUMNPROPERTY(a.id,a.name,'PRECISION') as 長度,isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小數位元,(case when a.isnullable=1 then '1'else
'0' end) 允許空,isnull(g.[value],'') AS 欄位說明 FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name = 'input'
order by a.id,a.colorder

 

SQL 查看所有表名：
select name from sysobjects where type='U'

查詢表的所有欄位名：
Select name from syscolumns Where ID=OBJECT_ID('表名')

select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
ACCESS
查看所有表名：
select name from MSysObjects where type=1 and flags=0

MSysObjects是系統對象，預設情況是隱藏的。通過工具、選項、視圖、顯示、系統對象可以使之顯示出來。

 

 SQL查詢表的備忘說明

SELECT 表名 = case when a.colorder = 1 then d.name
                   else '' end,
       表說明 = case when a.colorder = 1 then isnull(f.value, '')
                     else '' end
FROM syscolumns a
       inner join sysobjects d
          on a.id = d.id
             and d.xtype = 'U'
             and d.name <> 'sys.extended_properties'
       left join sys.extended_properties   f
         on a.id = f.major_id
            and f.minor_id = 0

SQL查詢表的所有欄位的備忘說明

SQL_1：

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
  LEFT OUTER JOIN sys.extended_properties ON
  ( sys.extended_properties.minor_id = syscolumns.colid
    AND sys.extended_properties.major_id = syscolumns.id)
  LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
  WHERE syscolumns.id IN
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid

SQL_2：

SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'欄位序號',
a.name N'欄位名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'標識',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
           (SELECT name
          FROM sysindexes
          WHERE (id = a.id) AND (indid in
                    (SELECT indid
                   FROM sysindexkeys
                   WHERE (id = a.id) AND (colid in
                             (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
        (xtype = 'PK'))>0 then '√' else '' end) N'主鍵',
b.name N'類型',
a.length N'佔用位元組數',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位元',
(case when a.isnullable=1 then '√'else '' end) N'允許空',
isnull(e.text,'') N'預設值',
isnull(g.[value],'') AS N'欄位說明'
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
order by object_name(a.id),a.colorder

 

SQL 查看所有表名：
select name from sysobjects where type='U'

查詢表的所有欄位名：
Select name from syscolumns Where ID=OBJECT_ID('表名')

select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns
ACCESS
查看所有表名：
select name from MSysObjects where type=1 and flags=0

MSysObjects是系統對象，預設情況是隱藏的。通過工具、選項、視圖、顯示、系統對象可以使之顯示出來。

 

 SQL查詢表的備忘說明

SELECT 表名 = case when a.colorder = 1 then d.name
                   else '' end,
       表說明 = case when a.colorder = 1 then isnull(f.value, '')
                     else '' end
FROM syscolumns a
       inner join sysobjects d
          on a.id = d.id
             and d.xtype = 'U'
             and d.name <> 'sys.extended_properties'
       left join sys.extended_properties   f
         on a.id = f.major_id
            and f.minor_id = 0

SQL查詢表的所有欄位的備忘說明

SQL_1：

SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME,
systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH,
sys.extended_properties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as
COLUMN_DEFAULT,syscolumns.isnullable as IS_NULLABLE FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
  LEFT OUTER JOIN sys.extended_properties ON
  ( sys.extended_properties.minor_id = syscolumns.colid
    AND sys.extended_properties.major_id = syscolumns.id)
  LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
  WHERE syscolumns.id IN
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')
    ORDER BY syscolumns.colid

SQL_2：

SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'欄位序號',
a.name N'欄位名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
end) N'標識',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
           (SELECT name
          FROM sysindexes
          WHERE (id = a.id) AND (indid in
                    (SELECT indid
                   FROM sysindexkeys
                   WHERE (id = a.id) AND (colid in
                             (SELECT colid
                            FROM syscolumns
                            WHERE (id = a.id) AND (name = a.name))))))) AND
        (xtype = 'PK'))>0 then '√' else '' end) N'主鍵',
b.name N'類型',
a.length N'佔用位元組數',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位元',
(case when a.isnullable=1 then '√'else '' end) N'允許空',
isnull(e.text,'') N'預設值',
isnull(g.[value],'') AS N'欄位說明'
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id
order by object_name(a.id),a.colorder