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
;