sql 获取数据库中所有表和视图的列_sql 获取db中所有表列-优快云博客

本文链接：https://blog.youkuaiyun.com/yucaoye/article/details/48004677

创建了一个名为[V_DB_TableInfo]的视图，用于显示SQL Server 2005数据库中所有表和视图的列信息，包括列顺序、列名、是否为主键、数据类型、允许空值及默认值等详细属性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [CV8]
GO

create view [dbo].[V_DB_TableInfo]
as

SELECT
--(case when a.colorder=1 then d.name else '' end) 表名,
a.colorder ColumnOrder,
d.name TableName,
a.name ColumnName,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else '' end) 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 '' end) IsPrimaryKey,
b.name ColumnType,
--a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as ColumnLength,
--isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then 1 else '' end) AllowNull,
isnull(e.text,'') DefaultValue
--isnull(g.[value],'') AS 字段说明
, (case when columnproperty(object_id(d.name),a.name,N'IsComputed')=1 then 1 else '' end ) IsComputed
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype in ('U','V') 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

GO