Get DDL for any SQL 2005 table

Question

Get DDL for any SQL 2005 table

Viewing 15 posts - 46 through 60 (of 127 total)

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

kbleeker SSC Veteran Points: 267 More actions · Answer 1

Hi Lowell - I made a change which appears to have sorted out the comma, but I'd like to know whether removing the space padding might have som drawback which I haven't foreseen:

I've commented out the space padding between column names in the indexes section, since your LEFT() was only removing one character, AFTER adding the space -

SELECT QUOTENAME(COLS.[name]) + ',' --+ ' '

Thanks,

Kevin

Lowell SSC Guru Points: 323513 More actions · Answer 2

kbleeker (10/18/2011)
Hi Lowell - I made a change which appears to have sorted out the comma, but I'd like to know whether removing the space padding might have som drawback which I haven't foreseen:
I've commented out the space padding between column names in the indexes section, since your LEFT() was only removing one character, AFTER adding the space -
SELECT QUOTENAME(COLS.[name]) + ',' --+ ' '
Thanks,
Kevin

Kevin you ROCK;

that's it, i think it has to do with whether ANSI_PADDING is on or off.

I was testing some more obscure things to try and recreate the scenario, and found an issue if your database was case Sensitive, the proc would not work, so i fixed that as well as adding the suggestion you identified...same links above, just updated.

mea culpa on the error, should have seen that a long time ago.

Thank you!

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 3

Haha! Thanks!

Is it okay if I reply with another problem though? (Talk about a trial by fire, this DB is the devil!)

4 columns in this DB are datatype "real", and the sp_GetDDLa is getting column widths for them, which of course leads to:

Cannot specify a column width on data type real.

For the first instance it generated a width of (24) - not sure if this helps, but I'm going through all of the generated scripts now to find the other instances of real, I'll let you know what I find.

Lowell SSC Guru Points: 323513 More actions · Answer 4

ahh, i had seen that once before...i'll fix that right now.

I can't thank you enough; another pair of eyes, doing work outside of my typical work load and comfort zone helps enourmously.

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!

Lowell SSC Guru Points: 323513 More actions · Answer 5

OK updated yet again...same old links but new code.

sp_GetDDLa_Latest.txt (Returns Table)

sp_GetDDL_Latest.txt (Returns varchar(max) )

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 6

Now that, sir, is a thing of beauty!

Thanks so much for your ridiculously quick response, it's been a pleasure communicating with you!

I am still having some problems with this demonic database with indexes and tables with the same name, but I'll not pester you with that now 🙂

Let me show you my appreciation by sending you here http://suptg.thisisnotatrueending.com/archive/4805536/images/1244443242558.jpg

sudhirnune SSCrazy Points: 2342 More actions · Answer 7

Hi Friends,

I saw one issue with respect to the GET_DDL Script, issue is that NVARCHAR Data columns are Created with Double the Data Size of teh Actual Defined Size.

Can you Please Check.

elifestyle Grasshopper Points: 16 More actions · Answer 8

I can confirm this problem. Fix appears simple; change

CONVERT(VARCHAR,(COLS.[max_length]))

to

CONVERT(VARCHAR,(COLS.[max_length] / 2))

in two places.

sudhirnune SSCrazy Points: 2342 More actions · Answer 9

sudhirnune

SSCrazy

Points: 2342

February 28, 2012 at 9:13 am

#1452682

Thanks elifestyle

Lowell SSC Guru Points: 323513 More actions · Answer 10

Thanks for the input guys!

Updated yet again...same old links but new code.

sp_GetDDLa_Latest.txt (Returns Table)

sp_GetDDL_Latest.txt (Returns varchar(max) )

[/quote]

this version is greatly enhanced;

--scripts any object(table,proc, function,trigger)

--has the nvarchar fix so they are not doubled

--scripts #temp tables too!

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!

bartvdc SSC Journeyman Points: 81 More actions · Answer 11

I'm yet another user of this great procedure. Lowell, thanks for the work and sharing.

I'm using it to (re)create tables from within a java app. I just have some issue with the GO statement , it always fails just after it.

As a workaround I changed the procedure so that each GO is replaced by a semicolon ';' . That works for indexes but not for triggers('must be the first statement in a query batch').

I guess the GO statement needs the 'newline' & 'carriage returns' before & after. Apparently the CHAR(10) & CHAR(13) are not recognized as such.

Is there any user that solved such a problem ?

Bart

bartvdc

Lowell SSC Guru Points: 323513 More actions · Answer 12

bartvdc (12/11/2012)
I'm yet another user of this great procedure. Lowell, thanks for the work and sharing.
I'm using it to (re)create tables from within a java app. I just have some issue with the GO statement , it always fails just after it.
As a workaround I changed the procedure so that each GO is replaced by a semicolon ';' . That works for indexes but not for triggers('must be the first statement in a query batch').
I guess the GO statement needs the 'newline' & 'carriage returns' before & after. Apparently the CHAR(10) & CHAR(13) are not recognized as such.
Is there any user that solved such a problem ?
Bart
bartvdc

good point Bart! everyone using anything other than SQL Server Management Studio would have the same problem;

the GO is a batch separator for SSMS, and not an actual SQL Command; so you are doing it right by replacing it with semi colons., for sure.

Glad this is helping you out!

are you using the latest, which can also script any object or temp table?

Latest sp_GetDDL_Latest.txt which returns a varchar(max) string

Latest sp_GetDDLa_Latest.txt which returns a recordset

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!

bartvdc SSC Journeyman Points: 81 More actions · Answer 13

Yes , I'm using the latest version.

Putting the semicolon instead of the GO gives the ''must be the first statement in a query batch'' problem for triggers.

I will use the procedure 'as is' and split the result to different statements before sending them to the DB.

Thanks for the quick reply,

Bart

bartvdc SSC Journeyman Points: 81 More actions · Answer 14

Just had this error when using the statement : Cannot specify a column width on data type real.

I moved real to the group without length :

Removed real here:

WHEN TYPE_NAME(COLS.[user_type_id]) IN ('float') --,'real')

Added real here:

WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime','money','text','image','real')

Now it works.

Bart

nikus SSC Veteran Points: 212 More actions · Answer 15

Nice procedure, saves me a lot of work.

Thanks for sharing it.

I have made some small improvement to add the order (asc,desc) of the index columns.

Maybe you want to add it in your code too.

Look for:

SELECT COLS.[name] + ',' + ' '

FROM sys.index_columns IXCOLS

INNER JOIN sys.columns COLS

ON IXCOLS.column_id = COLS.column_id

AND IXCOLS.[object_id] = COLS.[object_id]

WHERE IXCOLS.is_included_column = 0

AND IDX.[object_id] = IXCOLS.[object_id]

AND IDX.index_id = IXCOLS.index_id

ORDER BY key_ordinal

FOR XML PATH('')

) AS index_columns_key,

and replace it with:

SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '

FROM sys.index_columns IXCOLS

INNER JOIN sys.columns COLS

ON IXCOLS.column_id = COLS.column_id

AND IXCOLS.[object_id] = COLS.[object_id]

WHERE IXCOLS.is_included_column = 0

AND IDX.[object_id] = IXCOLS.[object_id]

AND IDX.index_id = IXCOLS.index_id

ORDER BY key_ordinal

FOR XML PATH('')

) AS index_columns_key,

Just the first line (select part) was changed,

the rest of the code ist for easier finding.

Thanks again.