sql 查询所有表以及表结构

查询数据库中所有的表：

select [id], [name],0 as statu
from [sysobjects] where [type] = 'u' order by [name]

查询表结构：

--查询表结构
ALTER PROCEDURE [dbo].[ziduan]
    
    @name nvarchar(50)
AS
BEGIN
    if ISNULL(@name,'')<>''
    
    BEGIN
       select 
      CAST( ROW_NUMBER() over(order by a.object_id) as nvarchar(50) ) as 编号,
        c.TABLE_NAME as '表名',
       a.name as 字段名,
      Cast(
       (case c.DATA_TYPE+'' when 'varchar' then 'varchar('+CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)+')'
       when 'nvarchar' then 'nvarchar('+CONVERT(varchar(10),c.CHARACTER_MAXIMUM_LENGTH)+')'
       when 'decimal'  then 'decimal('+CONVERT(varchar(10),c.NUMERIC_PRECISION)+','+CONVERT(varchar(10),c.NUMERIC_SCALE)+')'
       else cast( c.DATA_TYPE as nvarchar(50)) end ) 
       as  nvarchar(50))AS 数据类型,
         (case c.IS_NULLABLE when 'YES' THEN 'Y' else 'N' end) as 是否为空,
         '' AS 性质,
         '' AS 取值范围,
       CAST(  ISNULL(b.value,'') as nvarchar(200) )as 描述
          FROM
         sys.columns a left join sys.extended_properties b
         on (a.column_id=b.minor_id and a.object_id=b.major_id)
         left join INFORMATION_SCHEMA.COLUMNS c on a.name=c.COLUMN_NAME
         where a.object_id=object_id(@name)
         and c.TABLE_NAME=@name order by a.column_id
    END
    else
    select '请输入表名'
END

整理所有表结构数据：

--所有表结构数据

select [id], [name],0 as statu
into #t
from [sysobjects] where [type] = 'u' order by [name]

 
create table #t2
(
    编号  varchar(50),          
        表名    varchar(50),       
        字段名   varchar(50),   
        
        数据类型   sql_variant, --varchar(50),   
        是否为空   varchar(50),   
        性质   varchar(50),   
        取值范围   varchar(50),   
        描述     nvarchar(200)
  
)

 

declare  @c int ;
select @c= COUNT(1) from #t  a where  a.statu=0
while(@c>0)
begin

declare @TableName nvarchar(50);
select top 1 @TableName=a.name from #t  a where a.statu=0

insert into #t2 exec   dbo.ziduan  @TableName

update a set a.statu=1 from #t  a where a.statu=0 and a.name=@TableName

set @c=@c-1;

 

 end
 
 
 select  distinct  a.表名  from #t2 a
 
 select  *   from #t2 a