sqlserver的查看表创建SQL

出处：https://blog.csdn.net/ChristopherChen/article/details/51900572

在一般的数据库系统中可使用 表名右键--[Script Table as]--[Create To]--XX 输出建表SQL语句，但在某些SQL Server系统中可能客户仅提供数据库查询权限，导致无法导出建表语句，此时可使用select语句查询系统表生成建表语句
模板语句如下：


Declare @SelectTableName varchar(400),@SelectSchemaName  varchar(400),@CreateSchemaName  varchar(400);
--查询表名
set @SelectTableName='AAA';
--查询Schema名
set @SelectSchemaName='BBB';
--生成Create Table语句的Schema名
set @CreateSchemaName='CCC';


with t1 as (
select t.name as tableName
    , c.name as columnName
    , ty.name as typeName
       , case c.is_nullable when 1 then 'null' else 'not null' end notNullConstraint
    , c.max_length as typeLength 
       ,case when c.max_length in (8000,1,-1,4,8) then c.name+' '+ty.name+',' else 
       c.name+' '+ty.name+'('+ convert(VARCHAR,c.max_length)+')'+',' end columnDef
--     ,c.name+' '+ty.name+'('+ convert(VARCHAR,c.max_length)+')'+',' columnDef
from sys.columns c inner join sys.tables t on t.object_id=c.object_id inner join sys.schemas sch on t.schema_id=sch.schema_id
    inner join sys.types ty on ty.system_type_id=c.system_type_id
where ty.name<>'sysname'
and t.name=@SelectTableName
and sch.name=@SelectSchemaName
--order by t.name,c.column_id
)
select 'Create Table '+@CreateSchemaName+'.'+@SelectTableName+'('+ left((select columnDef+' ' from t1 FOR XML PATH('')),len((select columnDef+' ' from t1 FOR XML PATH('')))-1)+');' CreateTableQuery