数据字典（SQL语句）

最新推荐文章于 2021-05-18 16:21:42 发布

jing12

最新推荐文章于 2021-05-18 16:21:42 发布

阅读量724

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server 文章标签： sql table join object insert null

本文链接：https://blog.csdn.net/jing12/article/details/5151537

SQL Server 专栏收录该内容

14 篇文章

订阅专栏

本文提供了一段SQL脚本，用于从SQL Server中抓取指定数据库的所有表及其相关字段的详细信息，包括字段名、类型、长度等，并将这些信息存储到临时表中以供进一步处理。

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

declare @tmp table
(
id int identity(1,1),
TableName varchar(100),
Column_name varchar(100),
Type varchar(50),
Lenght int,
Scale int,
Nullable varchar(1),
Defaults varchar(4000),
PrimaryKey varchar(1)
)

select iid = identity(int,1,1), * into #a from SysObjects where xtype = 'U'
declare @i int
declare @max int
declare @table varchar(100)

set @i = 1
select @max = max(iid) from #a

while @i <= @max
begin

    select @table = name from #a where iid = @i
    if @@rowcount > 0
    begin

        insert @tmp (TableName, Column_name, Type, Lenght, Scale, Nullable, Defaults,PrimaryKey)
        select @table, a.name, c.name, a.length, a.xscale, case a.isnullable when 0 then 'N' else 'Y' end, isnull(d.text,''), case when x.PrimaryKey is null then '' else x.PrimaryKey end
        from SysColumns a with(nolock)
        inner join (select * from SysObjects with(nolock) where xtype = 'U' and id = object_id(@table)) b on a.id = b.id
        inner join SysTypes c with(nolock) on a.xtype = c.xusertype
        left join syscomments d with(nolock) on a.cdefault = d.id
        left join
            (select f.id, colid, 'Y' as PrimaryKey from SysIndexKeys f with(nolock), SysIndexes e, SysObjects g
            where f.id = e.id and f.indid = e.indid and f.id = g.parent_obj and e.name = g.name
            and g.xtype = 'PK' and g. parent_obj = object_id(@table)) x on a.id = x.id and a.colid = x.colid
    end
    set @i = @i + 1
end

select * from @tmp