Is there an easy way to get the scripts of a table with only t-sql, not with the wizard?

Question

Post reply

Is there an easy way to get the scripts of a table with only t-sql, not with the wizard?

ricardo_chicas

SSCertifiable

Points: 5024
More actions
March 19, 2014 at 10:27 am

#396446

Hi I am trying to build something that needs to be very dynamic that mimic a replication, for that I will need to get the scripts of some of my tables, index included as to create the tables on another repository and apply changes, is there a way to get those scripts without the wizard in an easy way?
I can build the table getting info from sp_help and the indexes from other sources but it is a bit complex and I was looking for something more simple
Thanks!

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

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

djj SSCoach Points: 18831 More actions · Answer 1

See if this helps:

SELECT

DB_NAME() AS DatabaseName, T.[name] AS Table_Name, AC.[name] AS Column_Name,

AC.column_id AS Ordinal_Position, TY.[name] AS Data_type,

CASE WHEN AC.[precision] = 0 THEN AC.max_length ELSE AC.[precision] END AS CLength,

AC.is_nullable AS IsNullable,

OBJECT_DEFINITION(AC.default_object_id) AS Column_Default

FROM sys.tables AS T

INNER JOIN sys.all_columns AC ON T.[object_id] = AC.[object_id]

INNER JOIN sys.types TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]

WHERE T.[is_ms_shipped] = 0

ORDER BY 1, 2, 4;

brn2code SSC Enthusiast Points: 108 More actions · Answer 2

Have a look here: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

I believe that it's easier to use INFORMATION_SCHEMA views.

SELECT TABLE_SCHEMA,

TABLE_NAME,

COLUMN_NAME,

DATA_TYPE,

CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')'

WHEN DATA_TYPE IN('decimal', 'numeric') THEN '(' + CAST( NUMERIC_PRECISION AS varchar(10)) + ',' + CAST( NUMERIC_SCALE AS varchar(10)) + ')'

ELSE '' END,

CASE WHEN IS_NULLABLE = 'YES' THEN 'NULL' ELSE 'NOT NULL' END,

CASE WHEN DATA_TYPE IN( 'char', 'varchar', 'nchar', 'nvarchar') THEN COLLATION_NAME ELSE '' END

FROM INFORMATION_SCHEMA.COLUMNS

You could add more details if needed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

brn2code (3/19/2014)
Have a look here: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table

That was my point, but I didn't have an example ready. 😀

I'm making the link clickable to make it easier for others.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 5

Thanks everyone, really good stuff, I will need to add a few things, will post the results back when I am done

Lowell SSC Guru Points: 323504 More actions · Answer 6

yep it's possible, but it's not built in.

using the GUI or via SMO is infinitely easier; I know, because i poured weeks of effort down the TSQL-only method, and it's probably only 95% completed.

i made a script i contributed,and an article that gets a little bit of traffic as well; the current version scripts out any table, temp table, or procedure/function/view.

exec sp_GetDDLa TableName;

exec sp_GetDDLa #TempTable;

exec sp_GetDDLa ProcedureName;

see the thread for a lot of different versions from other people who took my version and adapted it to their own needs.

sp_GetDDLa_Latest.txt

it's like a thousand lines of code, and can still be improved when it comes to scripting the indexes details out.

the thread:

http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

and the four year old article from 2009:

Get DDL for any SQL 2005 table[/url]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!