sql-server - 在动态查询中创建表
问题描述
我有一个动态查询,可以创建一个包含两个字段的表。第一个字段来自#Table,第二个(CreateDate)以静态方式添加。
if object_id(N'tempdb..#Table') is not null
drop table #Table;
create table #Table
(
[SchemaName] nvarchar(100) not null
,[TableName] nvarchar(128) not null
,[FieldId] int
,[ColumnName] nvarchar(100)
,FieldDefinitionID int
,Ordinal int
,[Data Type] nvarchar(100)
,IsNullable bit
,IsIdentity bit
,HasDefault bit
,DefaultName nvarchar(100)
,DefaultDefinition nvarchar(100)
,[Description] nvarchar(100)
,HasCheckConstraint bit
,CheckConstraintName bit
,CheckConstraintDefinition bit
,PartitionIndexKey bit
,ObjectID int
);
insert into #Table
(
[SchemaName]
,[TableName]
,[FieldId]
,[ColumnName]
,Ordinal
,[Data Type]
,IsNullable
,IsIdentity
,HasDefault
,HasCheckConstraint
)
select
N'dbo'
,N'test'
,-6
,N'RecordId'
,1
,N'int'
,0
,0
,0
,0
insert into #Table
(
[SchemaName]
,[TableName]
,[FieldId]
,[ColumnName]
,Ordinal
,[Data Type]
,IsNullable
,IsIdentity
,HasDefault
,HasCheckConstraint
)
select
N'dbo'
,N'test'
,-6
,N'RecordId'
,1
,N'int'
,0
,0
,0
,0
select N'
create table [' + [t].[SchemaName] + N'].[' + [t].[TableName] + N']
(
['
+ [t].[ColumnName]
+ N'] '
+ [t].[Data Type]
+ case
when [t].[IsNullable] = 0
then N' not null '
else N' null '
end
+ case
when [t].[IsIdentity] = 1
then N' identity '
else N''
end
+ case
when [t].[HasDefault] = 1
then N' constraint [' + [t].[DefaultName] + N'] default ' + [t].[DefaultDefinition]
else N''
end
+
N'
,[CreateDate] datetime not null
);
exec sys.sp_addextendedproperty @name = N''EntityId''
,@value = N''' + cast(11 as nvarchar(max)) + N'''
,@level0type = N''Schema'', @level0name = N''' + [t].[SchemaName] + N'''
,@level1type = N''Table'', @level1name = N''' + [t].[TableName] + N''';
exec sys.sp_addextendedproperty @name = N''MessageId''
,@value = N''' + cast(3456 as nvarchar(max)) + N'''
,@level0type = N''Schema'', @level0name = N''' + [t].[SchemaName] + N'''
,@level1type = N''Table'', @level1name = N''' + [t].[TableName] + N''';
'
from #Table t
所以结果是这个创建语句:
create table [dbo].[test]
(
[RecordId] int not null
,[CreateDate] datetime not null
);
exec sys.sp_addextendedproperty @name = N'EntityId'
,@value = N'11'
,@level0type = N'Schema', @level0name = N'dbo'
,@level1type = N'Table', @level1name = N'test';
exec sys.sp_addextendedproperty @name = N'MessageId'
,@value = N'3456'
,@level0type = N'Schema', @level0name = N'dbo'
,@level1type = N'Table', @level1name = N'test';
我希望得到相同的结果,但我需要从#Table 以动态方式获取第二个字段,就像第一个字段一样。但我不知道我该怎么做。
解决方案
请试试这个:
create table #Table
(
[SchemaName] nvarchar(100) not null
,[TableName] nvarchar(128) not null
,[FieldId] int
,[ColumnName] nvarchar(100)
,FieldDefinitionID int
,Ordinal int
,[Data Type] nvarchar(100)
,IsNullable bit
,IsIdentity bit
,HasDefault bit
,DefaultName nvarchar(100)
,DefaultDefinition nvarchar(100)
,[Description] nvarchar(100)
,HasCheckConstraint bit
,CheckConstraintName bit
,CheckConstraintDefinition bit
,PartitionIndexKey bit
,ObjectID int
);
GO
insert into #Table
(
[SchemaName]
,[TableName]
,[FieldId]
,[ColumnName]
,Ordinal
,[Data Type]
,IsNullable
,IsIdentity
,HasDefault
,HasCheckConstraint
)
values
(
N'dbo'
,N'test'
,-6
,N'RecordId'
,1
,N'int'
,0
,0
,0
,0
);
insert into #Table
(
[SchemaName]
,[TableName]
,[FieldId]
,[ColumnName]
,Ordinal
,[Data Type]
,IsNullable
,IsIdentity
,HasDefault
,HasCheckConstraint
)
values
(
N'dbo'
,N'test'
,-6
,N'NewField'
,2
,N'NewFieldType'
,0
,0
,0
,0
);
GO
declare @NewLine nchar(2) = nchar(13) + nchar(10);
with [Tables] AS
(
select distinct
[SchemaName],
[TableName],
N'[' + [SchemaName] + N'].[' + [TableName] + N']' AS [FullTableName]
from
#Table
)
select
N'create table ' + [FullTableName] + @NewLine +
N'(' + @NewLine +
N' ' + REPLACE(STUFF((select
N',[' + [ColumnName] + N'] '
+ [Data Type] +
+ case when [IsNullable] = 0 then N' not null' else N' null' end
+ case when [IsIdentity] = 1 then N' identity' else N'' end
+ case when [HasDefault] = 1 then N' constraint [' + [DefaultName] + N'] default ' + [DefaultDefinition] else N'' end
from
#Table
where
[SchemaName] = T.[SchemaName] AND
[TableName] = T.[TableName]
order by
[Ordinal]
for xml path('')
), 1, 1, N''
), N',', @NewLine + N' ,') + @NewLine +
N' ,[CreateDate] datetime not null' + @NewLine +
N');' + @NewLine +
@NewLine +
N'exec sys.sp_addextendedproperty @name = N''EntityId''' + @NewLine +
N' ,@value = N''' + cast(11 as nvarchar(max)) + N'''' + @NewLine +
N' ,@level0type = N''Schema'', @level0name = N''' + [t].[SchemaName] + N'''' + @NewLine +
N' ,@level1type = N''Table'', @level1name = N''' + [t].[TableName] + N''';' + @NewLine +
@NewLine +
N'exec sys.sp_addextendedproperty @name = N''MessageId''' + @NewLine +
N' ,@value = N''' + cast(3456 as nvarchar(max)) + N'''' + @NewLine +
N' ,@level0type = N''Schema'', @level0name = N''' + [t].[SchemaName] + N'''' + @NewLine +
N' ,@level1type = N''Table'', @level1name = N''' + [t].[TableName] + N''';' + @NewLine
from
[Tables] as T
order by
T.[SchemaName],
T.[TableName];
推荐阅读
- python - 如何从 AWS Lambda 的 s3 存储桶中读取 csv 文件?
- python - 根据指定的参数修改函数中的内部操作-python pandas
- ios - Swift 将 UITextView 文本保存为 .Pdf、.Doc 和 .Txt 文件格式并显示
- javascript - 调用ajax嵌入html页面后导航栏被破坏
- json - Google App 脚本 - 输出和解析数据时出现问题
- javascript - 请求后端输入更改数据的有效方法
- haskell - 重叠实例错误的原因是什么?
- javascript - 使用 Three.js 创建相对于视口大小的 BoxGeometry 16x16 网格
- c# - 如何在 Intellisense 和 DocFx 的 XML 注释中查看 cref 中的内容?
- swift - validateMenuItem 不为每个菜单项调用