自动生成与现有表匹配的用户定义表类型 [英] Automatically generate a user defined table type that matches an existing table

问题描述

我的数据库中已经存在多个表.其中一些有相当多的列.

I have several tables that already exist in my database. Some of them have quite a few columns.

我想制作一些存储过程来对这些表执行合并语句.为此，我希望存储过程的参数是用户定义的表类型.

I want to make some stored procedures to do a merge statement with these tables. To do that I would like the parameter of the stored procedure to be a User Defined Table type.

我可以编写每个表的脚本并将其修改为用户定义的表类型创建语句.

I can go script out each table and modify it to a User Defined Table Type Creation statement.

但我真正想要的是一种从数据库中现有表生成用户定义表类型的方法.然后我可以将该脚本添加到我的数据库构建中(然后添加新列对一张表不需要多次编辑).

But what I would really like is a way to generate a user defined table type off an existing table in my database. I could then add that script to my database build (and then adding new columns to a table does not need more than one edit).

有没有办法做到这一点?还是我应该忙于编写表格?

Is there a way to do that? Or should I just get busy scripting my tables out?

推荐答案

我不时需要同样的东西.这是我放在一起的一个小脚本.这有点粗糙，我不会相信我的生活，但它对我的情况相当有效.它没有脚本键，但对于我的场景来说这是没有必要的.不过，我使用的是 SQL 2012，所以我不完全确定这会像在 SQL 2008 上一样工作.我没有针对一些更奇特"的类型(例如 geometry、)对其进行测试>geography 和朋友们，因为我从来不需要使用它们.

I need the same thing from time to time. Here's a small script I put together. It's a bit rough and I wouldn't trust it with my life, but it works reasonably well for my case. It doesn't script keys, but for my scenario that's not necessary. I'm on SQL 2012 though, so I'm not completely sure this will work as is on SQL 2008. I did not test it for some of the more 'exotic' types like geometry, geography and friends, as I never needed to use them.

declare
    @tablename nvarchar(50)='Users',
    @schemaname nvarchar(50)='dbo',
    @sql nvarchar(max)=N'';

select @sql += N',' + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'[' + c.COLUMN_NAME + N'] [' + DATA_TYPE + N']'
    + case when c.CHARACTER_MAXIMUM_LENGTH is not null then N'(' + case c.CHARACTER_MAXIMUM_LENGTH when -1 then 'max' else cast(c.CHARACTER_MAXIMUM_LENGTH as nvarchar(10)) end + N')' else N'' end
    + case when c.DATA_TYPE = N'numeric' then N'('+CAST(NUMERIC_PRECISION as nvarchar(10))+N', '+CAST(NUMERIC_SCALE as nvarchar(10))+N')' else N'' end
    + case when c.is_nullable <> N'NO' then N' NULL' else N' NOT NULL'end
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = @tablename AND TABLE_SCHEMA = @schemaname
order by ORDINAL_POSITION;

set @sql = stuff(@sql, 1, 1, N'CREATE TYPE [' + @schemaname + N'].[tab_' + @tablename + N'] AS TABLE(')
    + nchar(13) + nchar(10) + ')' + nchar(13) + nchar(10) + 'GO';
set @sql += nchar(13) + nchar(10) + '--GRANT EXEC ON TYPE::[' + @schemaname + N'].[tab_' + @tablename + N'] TO [User];'
    + nchar(13) + nchar(10) + '--GO';

print @sql
-- If you're happy with the sql, you can pass it to sp_executesql to create your type
-- exec sp_executesql @sql;

这篇关于自动生成与现有表匹配的用户定义表类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！