Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»

Get DDL for any SQL 2005 table Expand / Collapse
Author
Message
Posted Tuesday, October 18, 2011 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 19, 2011 2:40 AM
Points: 9, 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 :)

Let me show you my appreciation by sending you here http://suptg.thisisnotatrueending.com/archive/4805536/images/1244443242558.jpg
Post #1192346
Posted Monday, January 30, 2012 8:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
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.
Post #1243685
Posted Monday, February 27, 2012 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 28, 2012 10:00 AM
Points: 2, 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.
Post #1258441
Posted Tuesday, February 28, 2012 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 6, 2014 11:47 PM
Points: 86, Visits: 214
Thanks elifestyle
Post #1258986
Posted Tuesday, February 28, 2012 9:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1259012
Posted Tuesday, December 11, 2012 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 14, 2012 4:54 AM
Points: 3, 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


Post #1395119
Posted Tuesday, December 11, 2012 10:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 12,953, Visits: 32,483
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1395228
Posted Wednesday, December 12, 2012 12:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 14, 2012 4:54 AM
Points: 3, 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
Post #1395470
Posted Wednesday, December 12, 2012 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 14, 2012 4:54 AM
Points: 3, 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
Post #1395619
Posted Tuesday, February 19, 2013 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 6:48 AM
Points: 4, 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.
Post #1421661
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse