查询库中所有表结构按照一定格式显示

最新推荐文章于 2023-12-20 17:32:50 发布

原创最新推荐文章于 2023-12-20 17:32:50 发布 · 678 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #table #insert #delete #join #scroll

SQL 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了一个SQL脚本，用于从系统元数据中提取表结构信息，并将其存储为两个临时表。脚本首先设置SQL查询选项，然后定义一个游标以遍历系统表元数据，提取表名、字段属性等信息。脚本通过循环处理游标结果，将数据插入到两个临时表中，并最终输出处理后的表结构信息。

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure sc_lxx5 as
--定义游标
DECLARE test_Cursor CURSOR SCROLL FOR

SELECT
表名=case when a.colorder=1 then d.name else '1' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
精度=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '1'else '0' end
FROM syscolumns a
left join systypes b on a.xusertype=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
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by d.name
--创建表table_1,如果存在则删除
if exists(select * from sysobjects where name= 'table_1')
drop table table_1

CREATE TABLE table_1
( code varchar(4000) not null DEFAULT (' '),
name1 varchar(50) not null DEFAULT (' '),
flag varchar(50) not null DEFAULT (' '),
type varchar(50) not null DEFAULT (' '),
lengthz varchar(50) not null DEFAULT (' '),
len1 varchar(50) not null DEFAULT (' '),
jd varchar(50) not null DEFAULT (' '),
issnull varchar(50) not null DEFAULT (' ')
)

CREATE TABLE #table_24008
( code varchar(4000) not null DEFAULT (' '),
name1 varchar(50) not null DEFAULT (' '),
flag varchar(50) not null DEFAULT (' '),
type varchar(50) not null DEFAULT (' '),
lengthz varchar(50) not null DEFAULT (' '),
len1 varchar(50) not null DEFAULT (' '),
jd varchar(50) not null DEFAULT (' '),
issnull varchar(50) not null DEFAULT (' ')
)
CREATE TABLE #t
( code varchar(4000) not null DEFAULT (' '),
name1 varchar(50) not null DEFAULT (' '),
flag varchar(50) not null DEFAULT (' '),
type varchar(50) not null DEFAULT (' '),
lengthz varchar(50) not null DEFAULT (' '),
len1 varchar(50) not null DEFAULT (' '),
jd varchar(50) not null DEFAULT (' '),
issnull varchar(50) not null DEFAULT (' ')
)
/*
CREATE TABLE #t25
( no1 varchar(4000) not null DEFAULT (' '),
code varchar(4000) not null DEFAULT (' '),
name1 varchar(50) not null DEFAULT (' '),
flag varchar(50) not null DEFAULT (' '),
type varchar(50) not null DEFAULT (' '),
lengthz varchar(50) not null DEFAULT (' '),
len1 varchar(50) not null DEFAULT (' '),
jd varchar(50) not null DEFAULT (' '),
issnull varchar(50) not null DEFAULT (' ')
)
*/
--打开游标
OPEN test_Cursor
--使用游标
DECLARE @tablename sysname
DECLARE @code varchar(4000)
DECLARE @name varchar(50)
DECLARE @flag varchar(50)
DECLARE @type varchar(50)
DECLARE @lengthz varchar(6)
DECLARE @len varchar(50)
DECLARE @jd varchar(50)
DECLARE @issnull varchar(50)

WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM test_Cursor INTO @tablename,@code,@name,@flag,@type,@lengthz,@len,@jd,@issnull
if(@tablename <> '1')
BEGIN
  --每次插入表头时将上个表的数据插入上个表中
  if exists(select * from #table_24008)
  BEGIN
   /*--insert into table_1
   select   identity(int,1,1) as ccode,name1,flag,type,lengthz,len1,jd,issnull from #table_24008 order by name1
   --select   *   from   #t
   delete from #table_24008
   identity(int,1,1) as no1,code,name1,flag,type,lengthz,len1,jd,issnull
   */
   /*select identity(int,1,1) as no1,code,name1,flag,type,lengthz,len1,jd,issnull into   #t   from
   (select code,name1,flag,type,lengthz,len1,jd,issnull from #table_24008 order by name1) a
    select   *   from   #t order by name1*/
   insert into #t
   select * from #table_24008 order by name1


   select identity(int,1,1) as no1 ,name1,flag,type,lengthz,len1,jd,issnull
   into #t25 from #t

insert into table_1
select no1,name1,flag,type,lengthz,len1,jd,issnull from #t25

   delete from #table_24008
   delete from #t
   drop table #t25
  END
  insert into table_1 values(' ',' ',' ',' ',' ',' ',' ',' ')
  insert into table_1(name1,flag)values('表名(物理):',@tablename)
  insert into table_1 values('字段序号','字段名','主键','类型','占用字节数','长度','精度','允许空')
  --insert into table_1 values(@code,@name,@flag,@type,@lengthz,@len,@jd,@issnull)

END

--PRINT @code,@name,@flag,@type,@lengthz,@len,@jd,@issnull
--PRINT @code
--PRINT @name
insert into #table_24008 values(@code,@name,@flag,@type,@lengthz,@len,@jd,@issnull)

END
--插入最后一个表的字段数据
BEGIN
   insert into #t
   select * from #table_24008 order by name1


   select identity(int,1,1) as no1 ,name1,flag,type,lengthz,len1,jd,issnull
   into #t26 from #t

insert into table_1
select no1,name1,flag,type,lengthz,len1,jd,issnull from #t26

   delete from #table_24008
   delete from #t
   drop table #t26
END
--关闭游标
CLOSE test_Cursor

--释放游标
DEALLOCATE test_Cursor

select * from table_1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO