Need to script table

Question

Need to script table

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
July 18, 2007 at 2:39 pm

#153687

Hi all,
I need to create a table definition script. Unfortunately, object_definition function does not work for tables. Is there any other similar function, or trick, script for this ?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Daryl AZ Hall of Fame Points: 3087 More actions · Answer 1

Not sure if this would fit your needs. Not an elegant solution... But it shouldn't be too much of a stretch to have some string manipulation to turn the following into a simplified create script.

select col.column_NAME + ' ' + data_type

+ CASE WHEN data_type IN ('char', 'varchar') THEN '('+ CAST(character_maximum_length AS VARCHAR(5)) + ')' ELSE '' end

from information_schema.columns col

WHERE col.TABLE_NAME = 'targetTable'

ORDER BY col.ordinal_position

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Can't you use the scripting methods built-in the GUI!

sumit-314810 SSC-Addicted Points: 402 More actions · Answer 3

You can use VB.NET scripting

This talks about scripting all the table in a database.

How to: Script Out the Dependencies for a Database in Visual Basic .NET

http://msdn2.microsoft.com/en-us/library/ms162160.aspx

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 4

Why can't you use the Generate Script that is readily available to you in SQL Server.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

What are you using the object_definition function for?

Also, what if you do this in two steps: 1st, script the table. 2nd, modify the table to add the object_definition component. would that work in the framework you want and in the way you want to do it? As opposed to the other means suggested to you?

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 6

Thanks, Daryl, for your code. I modified it and it is what I want. What I needed is a simple code that scripts out all tables from database in standard form like:

create table

(column1_name type(size),

column2_name type(size))

Answering to other replies, yes while searching on internet priorly to posting this question here I also found a lot of references to VB and CMO scripting. But in my case it has to be stored procedure, so anybody who has at least dbo rights would be able to connect and run it if needed. Moreover, SQL solution looks more compact and intuitive. We don't use GUI for this because this procedure may will be scheduled as a job.

Steve, I am using object_definition function to generate object scripts. It works fine for stored procedures, functions, views, however by some reason it does not work for tables.