SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get DDL for any SQL 2005 table


Get DDL for any SQL 2005 table

Author
Message
kbleeker
kbleeker
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 30
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 Smile

Let me show you my appreciation by sending you here http://suptg.thisisnotatrueending.com/archive/4805536/images/1244443242558.jpg
sudhirnune
sudhirnune
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 252
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
elifestyle
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 16
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
sudhirnune
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 252
Thanks elifestyle
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73921 Visits: 40974
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
bartvdc
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 9
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
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73921 Visits: 40974
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
bartvdc
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 9
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
bartvdc
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 9
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
nikus
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search