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 ««12345»»»

Get DDL for any SQL 2005 table Expand / Collapse
Author
Message
Posted Tuesday, April 6, 2010 11:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:44 PM
Points: 21, Visits: 252
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
Post #898176
Posted Wednesday, April 7, 2010 5:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 12,881, Visits: 31,817
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

--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 #898354
Posted Wednesday, April 7, 2010 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:44 PM
Points: 21, Visits: 252
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
Post #898784
Posted Wednesday, April 7, 2010 11:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 12,881, Visits: 31,817
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

--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 #898835
Posted Wednesday, April 7, 2010 12:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 21,212, Visits: 14,910
Thanks Lowell for a superb script.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #898856
Posted Wednesday, April 7, 2010 12:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:44 PM
Points: 21, Visits: 252

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
Post #898857
Posted Wednesday, August 18, 2010 7:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 1:41 PM
Points: 10, 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?


Post #971121
Posted Wednesday, August 18, 2010 7:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 12,881, Visits: 31,817
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

--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 #971137
Posted Wednesday, August 18, 2010 9:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 12,881, Visits: 31,817
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

--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 #971223
Posted Wednesday, August 18, 2010 9:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 1:41 PM
Points: 10, Visits: 79
Thanks for the credits, but my input was only a few lines of code:)

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.


Post #971227
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse