Click here to monitor SSC
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
David In BC
David In BC
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 371
There's a bug in this script at approximately line 82 - details below.

At line 56, you go to the trouble of figuring out the OBJECT_ID for the table using the schema name, etc:

SELECT
@TABLE_ID = [object_id]
FROM sys.objects
WHERE [type] IN ('S','U','V')
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;


And then a bit farther down, you cancel out all that good work, and re-set the TABLE_ID. If your table is in a schema other than the dbo schema, the @TABLE_ID gets set to NULL, and the stored procedure returns no results.


-- Valid Table, Continue Processing
SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( '
SELECT @TABLE_ID = OBJECT_ID(@TBLNAME)


David
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14956 Visits: 38953
wow excellent catch David! I rarely work under multiple schemas, and certainly missed this!

I've updated my code and previous links to have the correction you mentioned, and you can downloaded it here if needed:

here is the updated version: sp_GetDDL2005_V307

I've got a function that uses the same code, I'll need to fix that as well.
Thanks David!

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!

David In BC
David In BC
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 371
No problem, and thanks for the excellent script.

I am planning to use a modified version to help manage our internal database version control - roll up patch directories back into the main branch, and I needed something to pull out all the tables.

David
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14956 Visits: 38953
yeah i've used this code for a "schema checker" which compares a database to various xml docs;
based on an internal table, if your db is marked "version 4" or "version4.1", etc , it uses the appropriate xml doc from a website to check for objects;anything missing in the target database gets the script that was generated from this proc; works well for me;

I even have a version generates the results to create the same table in an Oracle database...creates triggers for the identity(), changes datatypes to NUMBER,VARCHAR2, etc.

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!

SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21075 Visits: 18259
Thanks Lowell for a superb script.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

David In BC
David In BC
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 371
Another quick fix - the size computation is missing for columns of type "varbinary" - I added this to the long query that computes the column type, length, and nullability:

WHEN TYPE_NAME(sys.columns.[user_type_id]) = 'varbinary'
THEN
CASE WHEN sys.columns.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
ELSE '('
+ CONVERT(VARCHAR,(sys.columns.[max_length]))
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END


There is also a bug in the nvarchar size handling - the "-1" isn't enough, as a NVARCHAR(MAX) has a length of 8000 - an inconsistency within SQL Server.... I haven't tackled that yet - just did a search and replace.

David
Vincent Wylenzek
Vincent Wylenzek
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 79
Thanks for this script, it has been a great help!

I extended the script with a few lines and now it returns also the DDL for views, procedures and functions.

--##############################################################################
-- Check If TableName is Valid
--##############################################################################
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,
@TBLNAME = PARSENAME(@TBL,1)
SELECT
@TABLE_ID = [object_id]
FROM sys.objects
--WHERE [type] = 'U'
WHERE [type] in ('V','P','FN' )
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']'
SELECT @FINALSQL
END
ELSE
BEGIN
SET @FINALSQL = 'SELECT S.TEXT FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON S.ID = O.OBJECT_ID WHERE O.NAME = ''' + @TBLNAME + ''''
EXEC (@FINALSQL)
--SELECT @FINALSQL
END
RETURN 0
END


Question: The CrLf is not working in the output, the characters do work if I run an print command of those chars (13, 10), but in the output there is no CrLF available. Should I convert to XML?
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14956 Visits: 38953
glad it's helping you out Vincent;

you know that's a neat idea and exactly why i love SSC; other peoples feedback make everything better.

Sometimes you cannot see the tree when you are standing in the forest: extending sp_getDDL to return any object definition just makes sense. I have an updated version of that that gets the definition of any regular or TEMP table (#tempname) as well;
also i have another version that splits the text results of the varchar(max) on vbCrLf, and returns a multi-row resultset; i fiddle with this thing all the time to make it better.

As for the CrLf issue, i suspect that the problem is this:

I think that maybe the text is syscomments is delimited with just CHAR(13), and not CHAR(13) + CHAR(10);

i think that i would do a double replace to fix it and see:
SET @FINALSQL = 'SELECT  S.TEXT FROM SYSCOMMENTS....
--becomes
SET @FINALSQL = SELECT REPLACE(REPLACE(S.TEXT,CHAR(13),CHAR(13) + CHAR(10)), CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10) ) FROM SYSCOMMENTS S [more]





i'm going to play with your example, i might change it so it hits sys.sql_modules instead of syscomments, but that is an awesome idea, and thank you so much for the feedback!

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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14956 Visits: 38953
dayum that made for a really nice enhancement, Vincent.

here's links to two updated versions of the script.

sp_GetDDL V 309 returns the definition as a single varchar(max) of any of the main objects: Table, TempTable, View, Procedure, Function or Trigger.

sp_GetDDLa V 309a returns the definition as one or more rows of data, the varchar(max) split on vbCrLf of any of the main objects: Table,TempTable, View, Procedure, Function or Trigger.

here's a recap of some of the improvements made so far:

-- USAGE: exec sp_GetDDLa [TableName] | [#TempTableName] |[ProcedureName] | [ViewName] [FunctionName] |[TriggerName]
-- or exec sp_GetDDLa YourObjectName
-- or exec sp_GetDDLa 'bob.example'
-- or exec sp_GetDDLa '[schemaname].[ObjectName]'

-- V300 uses String concatination and sys.tables instead of a cursor
-- V301 enhanced 07/31/2009 to include extended properties definitions
-- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found
-- V303 fixes an issue where all rules are appearing, instead of jsut the rule related to a column
-- V304 testing whether vbCrLf is better than just CHAR(13), some formatting cleanup with GO statements
-- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]
-- V305 user feedback helped me find that the type_name function should call user_type_id instead of system_type_id
-- also fixed issue where identity definition missing from numeric/decimal definition
-- V306 fixes the computed columns definition that got broken/removed somehow in V300
-- also formatting when decimal is not an identity
-- V307 fixes bug identified by David Griffiths-491597 from SSC where the @TABLE_ID
-- is reselected, but without it's schema , potentially selecting the wrong table
-- also fixed is the missing size definition for varbinary, also found by David Griffith
-- V308 abtracted all SQLs to use Table Alaises
-- added logic to script a temp table.
-- added warning about possibly not being marked as system object.
-- V309 added logic based on feedback from Vincent Wylenzek @SSC to return the definition from sys.sql_modules for
-- any object like procedure/view/function/trigger, and not just a table.
-- note previously, if you pointed sp_GetDDL at a view, it returned the view as a table...
-- now it will return the view definition instead.
-- V309 returns single varchar(max)
-- V309a returns multi row recordset, one line per record of the varchar(max) split on vbCrLf



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!

Vincent Wylenzek
Vincent Wylenzek
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 79
Thanks for the credits, but my input was only a few lines of codeSmile

Your defenitely fast, thanks for providing the updates; I am working on an auditsystem
where you can combine the sp_getdll with audit events.
So every DDL event will be logged and the previous valid DDL-statement will be recorded by your statement.

If it is finished I will post it here.
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