SqlServer获取表结构DDL查询语句
需要根据实际情况更改代码中的schema_name 和 table_name,
查询结果sqlserver_ddl一整列就是对应表的DDL
--need to change the @schema_name and the table_name
declare @drop_statement varchar(40) ;
declare @create_statement varchar(40) ;
declare @primary_statement varchar(40) ;
declare @schema_name varchar(40);
set @drop_statement = 'drop table if exists ';
set @create_statement = 'create table ';
set @primary_statement = 'primary key( ';
set @schema_name = 'dbo';
select a.*
,case when CHARINDEX(a.column_name,b.pk_lists) > 0 then 'YES' else 'NO' end as is_PK
,(case
when rank() over(partition by a.table_name order by id) = 1
then
(concat(
concat(@drop_statement, @schema_name, '.',a.table_name, '; ',@create_statement, @schema_name, '.',a.table_name , ' ( ' ),
case when b.pk_lists is not null
then concat(@primary_statement, b.pk_lists, ' ) , ')
else '' end ,
concat('[',a.column_name, '] ' ,replace(data_type,'-1','max') collate chinese_prc_ci_as,
case when a.is_nullable = 'NO' then ' not null' else '' end)))
when rank() over(partition by a.table_name order by id desc) = 1
then
concat(' ,[' , a.column_name, '] ' ,replace(data_type,'-1','max'),
case when a.is_nullable = 'NO' then ' not null' else '' end, ' );' )
else concat(' ,[' , a.column_name, '] ' ,replace(data_type,'-1','max'),
case when a.is_nullable = 'NO' then ' not null' else '' end)
end
) as sqlserver_ddl
from
(
select NUMERIC_PRECISION,NUMERIC_SCALE
,row_number() over (order by table_schema,table_name,ordinal_position)as id
,table_schema as [schema]
,table_name,column_name
,case when data_type like '%char' or data_type like '%binary' then concat(data_type,'(',convert(nvarchar,CHARACTER_MAXIMUM_LENGTH),')')
when data_type in ('decimal','numeric') then concat(data_type,'(',convert(nvarchar,NUMERIC_PRECISION),',',convert(nvarchar,NUMERIC_SCALE),')')
else data_type end as data_type
,is_nullable
from information_schema.columns
--source table
where table_schema = @schema_name
and table_name in (
'xxx'
)
)a
left join
(
select distinct table_schema,table_name,pk_lists
from (
select a.*
, STUFF((SELECT ',' + column_name FROM information_schema.key_column_usage WHERE table_name = a.table_name and table_schema = a.table_schema FOR XML PATH('')), 1, 1, '') AS pk_lists
from information_schema.key_column_usage a
join information_schema.table_constraints b
on a.table_schema=b.table_schema and a.table_name=b.table_name and a.constraint_name=b.constraint_name
where b.constraint_type='PRIMARY KEY' and b.table_schema = @schema_name
)t
)b
on a.[schema]=b.table_schema and a.table_name=b.table_name
order by id
;