Automatically create 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 several columns.

I want some stored procedures to execute a merge statement with these tables. For this, I would like the stored procedure parameter to be a user-defined table type.

I can go script from each table and change it into a create table type user statement.

But I would like this method to generate a specific type of table from an existing table in my database. I could add this script to my database assembly (and then add new columns in the table do not need more than one edit).

Is there any way to do this? Or do I just need to write tables?

Answer 1

In SQL Server Management Studio, you can right-click the database and in the TASKS section select the script database. Select only the tables, and then the tables that interest you.

This does not give you the one store you need, but it can easily and quickly script easily and quickly. And then let you find and replace a bit to get what you need.

Answer 2

I never knew there was a wizard for creating database scripts, like the one that Dems talks about in his answer. And this seems like a more universal method than the one I used, because the wizard allows you to generate scripts for different types of objects at a time.

Nevertheless, I believe that I will share with you, it seems to me, a little easier and come in handy when you need only script objects of the same type as only tables.

So here (specifically for tables):

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

• Expand the Databases item.

• Expand the item with the name of your database.

• Click Tables.

• Open Object Explorer Information ( F7 ). Now it should display a list of user tables.

• Using standard Windows methods to select multiple objects (e.g. Ctrl + click), select the tables you want to use the script.

• Right-click on any of the selected items and select the script table as ▸, then select the type of script and where to save it.

When you need to script various types of objects, go to another Explorer Explorer folder instead of tables, for example. for stored procedures, this will be Programmability \ Stored Procedures.

Answer 3

I need the same from time to time. Here's a small script I put together. It's a little rude, and I will not trust this with my life, but it works well enough for my business. These are not script keys, but for my script this is not necessary. I'm on SQL 2012, though, so I'm not quite sure that this will work the same way as on SQL 2008. I have not tested it for some of the more "exotic" types, such as geometry , geography and friends, since I never did not have 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 4

I have not tried this, but I know that it works in temporary tables:

 SELECT * INTO NewTable FROM OldTable WHERE FALSE 

However, be careful that it copies everything you need, it will not use any keys, indexes or permissions.

Editorial: Thank you for the prompt user92546