Automatically generate a user defined table type that matches an existing table

Question

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?

Answer 1

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;

Answer 2

In SQL Server Management Studio you can right click a database, and under TASKS choose to script the database. Choose Tables only, and then the tables you're interested in.

It doesn't give you a one stop shop to what you want, but it can script many tables quickly and easily. And then allow you to do a bit of find-and-replace to get what you need.

Answer 3

I never knew there was a wizard for generating database scripts like the one Dems is talking about in their answer. And it seems a more universal method than the one I've been using, because the wizard allows you to generate scripts for different types of objects in one go.

Still, I guess I'll share mine, as it seems to me a little bit simpler and comes quite in handy when you only need to script same-type objects, like only tables.

So, here goes (specifically for tables):

1. Open Object Explorer (F8) and connect it to the target server instance.

2. Expand the Databases item.

3. Expand the item with your database name.

4. Click Tables.

5. Open Object Explorer Details (F7). It should now display the list of user tables.

6. Using standard Windows methods of selecting multiple objects (like Ctrl+click), select the tables you want to script.

7. Right-click on any of the selected items and choose Script Table as ▸, then pick the kind of script and where to save it.

When you need to script different types of objects, proceed to a different Object Explorer ‘folder’ instead of Tables, e.g. for stored procedures it would be Programmability\Stored Procedures.