Get DDL for any SQL 2005 table
-
anujkgarg (10/22/2015)
I downloaded the script from http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt, it does not work on SQL2008 R2. It does not print all the columns of the below table.CREATE TABLE dbo.Test1 ( id int NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );
Is there any other version available ?
it's not the code, it's your current settings in SSMS.
the proc it returns a varchar(max), and by default. SSMS will print only the first 256 characters, unless you explicitly change it:
if your table is bigger than 8000 chars, the proc returns the right data, but you can't use SSMS to preview the string; you'd need to stick it in a table, or convert to xml or something to see it in SSMS.
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! -
Thanks Lowell. I changed the settings as you suggested.
As you see above, my table lengths is less than 8000 bytes. After changing it to 8192 bytes, I am getting the below results -
--Non-Temp Table, [Test1] continue Processing
Item
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('[dbo].[Test1]') IS NOT NULL
DROP TABLE [dbo].[Test1]
GO
CREATE TABLE [dbo].[Test1] (
[name] VARCHAR(100) NULL,
CONSTRAINT [PK__Test1__3213E83F0D85BAD5] PRIMARY KEY CLUSTERED ([id] asc))
Here, "id int NOT NULL" is missing from output.
###############
This is the output using osql utility at command prompt-
IF OBJECT_ID('[dbo].[Test1]') IS NOT NULL
DROP TABLE [dbo].[Test1]
GO
CREATE TABLE [dbo].[Test1] (
[name] VARCHAR(100)
NULL,
CONSTRAINT [PK__Test1__3213E83F45CA13F8] PRIMARY KEY CLUSTERED ([id] asc))
-
i downloaded both code sets as a dbl check so i am not using an unpublished improved version., and it's working perfectly.
changes to settings in Options only affect newly opened tabs, so i'm not sure what the problem is on your side.
CREATE TABLE dbo.Test1 ( id int NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );
exec sp_GetDDL Test1
exec sp_GetDDLa Test1
the results:
IF OBJECT_ID('[dbo].[Test1]') IS NOT NULL
DROP TABLE [dbo].[Test1]
GO
CREATE TABLE [dbo].[Test1] (
[id] INT NOT NULL,
[name] VARCHAR(100) NULL,
CONSTRAINT [PK__Test1__3213E83F46F27704] PRIMARY KEY CLUSTERED ([id] asc))
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, I can also see the correct output in SQL2012 but not in SQL2008R2.
Below output is from SQL2012 on same SSMS instance. I did not use any other instance of SSMS to confirm that the issue is not due to SSMS settings.
IF OBJECT_ID('[dbo].[Test1]') IS NOT NULL
DROP TABLE [dbo].[Test1]
GO
CREATE TABLE [dbo].[Test1] (
[id] INT NOT NULL,
[name] VARCHAR(100) NULL,
CONSTRAINT [PK__Test1__3213E83F799791B6] PRIMARY KEY CLUSTERED ([id] asc))
-
ok, what does "does not work on SQL2008 R2" mean to you?
what, specific error or behavior do you get? did you try a differetn table as a proof of concept? if a column is missing in the definition, is it possible you have two different tables with the same name but different databases or schemas?
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! -
I created the different table in SQL2008R2 -
CREATE TABLE mydatabase.dbo.Test_New ( name1 varchar(10), name2 varchar(10), name3 VARCHAR(100) );
This database is having single table only. After executing the procedure, I observed the same issue -
exec sp_GetDDL '[dbo].[Test_New]'
Output
-------
IF OBJECT_ID('[dbo].[Test_New]') IS NOT NULL
DROP TABLE [dbo].[Test_New]
GO
CREATE TABLE [dbo].[Test_New] (
[name3] VARCHAR(100) NULL)
I am suspecting that sp_GetDDL procedure is retrieving last column of the table only. It is not including other two columns (name1, name2) into the output. I have debugged the procedure in SSMS and can see the same output in @FINALSQL variable (Get the columns... SECTION) and at line#1295 also
-
interesting;and i think i see the issue...my script might not be case sensitive.
is this database case sensitive?
can you run this command?
SELECT
DATABASEPROPERTYEX(db_name(),'Collation') AS Collation
i created a case sensitive db, and i get errors when running the code from master against a CS db:
Msg 4104, Level 16, State 1, Procedure sp_GetDDL, Line 355
The multi-part identifier "def.name" could not be bound.
Msg 4104, Level 16, State 1, Procedure sp_GetDDL, Line 355
The multi-part identifier "def.definition" could not be bound.
Msg 207, Level 16, State 1, Procedure sp_GetDDL, Line 415
Invalid column name 'object_id'.
Msg 207, Level 16, State 1, Procedure sp_GetDDL, Line 400
Invalid column name 'Rows'.
Msg 4104, Level 16, State 1, Procedure sp_GetDDL, Line 881
The multi-part identifier "def.definition" could not be bound.
Msg 207, Level 16, State 1, Procedure sp_GetDDL, Line 946
Invalid column name 'object_id'.
Msg 207, Level 16, State 1, Procedure sp_GetDDL, Line 931
Invalid column name 'Rows'.
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! -
Finally, I restarted my machine and script is working now as expected. My server collation is Chinese_PRC_CI_AS. Thanks!!!
-
Hi Lowell,
thanks for sharing this really useful script.
I have tried to add a detail in the section for the definition of the foreign keys: the aim is try to manage the "on delete", "on update" and the "not for replication" clauses.
Your original code:
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SET @FKSQLS = '' ;
SELECT
@FKSQLS=@FKSQLS
+ @vbCrLf
+ 'CONSTRAINT ' + quotename(OBJECT_NAME(constid)) + ''
+ SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) ))
+ ' FOREIGN KEY (' + quotename(COL_NAME(fkeyid,fkey))
+ ') REFERENCES ' + quotename(OBJECT_NAME(rkeyid))
+'(' + quotename(COL_NAME(rkeyid,rkey)) + '),'
FROM sysforeignkeys FKEYS
WHERE fkeyid = @TABLE_ID
Now integrated so:
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SELECT
@FKSQLS=@FKSQLS
+ @vbCrLf
+ 'CONSTRAINT ' + quotename(OBJECT_NAME(FKEYS.constid)) + ''
+ SPACE(@STRINGLEN - LEN(OBJECT_NAME(FKEYS.constid) ))
+ ' FOREIGN KEY (' + quotename(COL_NAME(FKEYS.fkeyid, FKEYS.fkey))
+ ') REFERENCES ' + quotename(OBJECT_NAME(FKEYS.rkeyid))
+'(' + quotename(COL_NAME(FKEYS.rkeyid, FKEYS.rkey)) + ') '
+ CASE WHEN sfk.delete_referential_action = 1 THEN N'ON DELETE CASCADE '
WHEN sfk.delete_referential_action = 2 THEN N'ON DELETE SET NULL '
WHEN sfk.delete_referential_action = 3 THEN N'ON DELETE SET DEFAULT '
ELSE '' END
+ CASE WHEN sfk.update_referential_action = 1 THEN N'ON UPDATE CASCADE '
WHEN sfk.update_referential_action = 2 THEN N'ON UPDATE SET NULL '
WHEN sfk.update_referential_action = 3 THEN N'ON UPDATE SET DEFAULT '
ELSE '' END
+ CASE WHEN sfk.is_not_for_replication = 1 THEN N'NOT FOR REPLICATION '
ELSE '' END
+ ','
FROM sysforeignkeys FKEYS
JOIN sys.foreign_keys sfk
ON FKEYS.fkeyid = sfk.parent_object_id
AND OBJECT_NAME(FKEYS.constid) = sfk.name
WHERE FKEYS.fkeyid = @TABLE_ID
I hope it can be helpful, let me know if there's a better way to extract these informations
Alberto
-
Hi all,
I've tried to make an improvement to the management of the extended properties, previously integrated (V314) based on "OmaCoders suggestion to script column extended properties as well" (http://www.sqlservercentral.com/Forums/FindPost1651606.aspx).
The aim is to manage extended properties even for constraint,index,trigger and parameter as well as for column, based on the information obtained by the Adam Aspin's article (http://www.sqlservercentral.com/articles/Metadata/72608/).
Original code:
--OMacoder suggestion for column extended properties http://www.sqlservercentral.com/Forums/FindPost1651606.aspx
SELECT @EXTENDEDPROPERTIES =
@EXTENDEDPROPERTIES + @vbCrLf +
'EXEC sys.sp_addextendedproperty
@name = N''' + [name] + ''', @value = N''' + REPLACE(convert(varchar(max),[value]),'''','''''') + ''',
@level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',
@level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',
@level2type = N''COLUMN'', @level2name = ' + quotename([objname]) + ';'
--SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, 'column', NULL)
Now integrated so:
WITH obj AS (
SELECT split.a.value('.', 'VARCHAR(20)') AS name
FROM (
SELECT CAST ('<M>' + REPLACE('column,constraint,index,trigger,parameter', ',', '</M><M>') + '</M>' AS XML) AS data
) AS A
CROSS APPLY data.nodes ('/M') AS split(a)
)
SELECT
@EXTENDEDPROPERTIES =
@EXTENDEDPROPERTIES + @vbCrLf + @vbCrLf +
'EXEC sys.sp_addextendedproperty
@name = N''' + lep.[name] + ''', @value = N''' + REPLACE(convert(varchar(max),lep.[value]),'''','''''') + ''',
@level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',
@level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',
@level2type = N''' + UPPER(obj.name) + ''', @level2name = ' + quotename(lep.[objname]) + ';'
--SELECT objtype, objname, name, value
FROM obj
CROSS APPLY fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, obj.name, NULL) AS lep;
Thanks to "bvr" (http://stackoverflow.com/a/16083088) and "Kshitij Satpute" (http://www.sqlservercentral.com/scripts/split+string/100950/) for the delimited string split approach.
Alberto
-
I am getting only the last column returned when i call sp_getddl with a tablename.
create table aaa (field1 varchar(1), field2 varchar(2), field3 varchar(3))
exec sp_getddl 'aaa'
If I comment out the very last "Get the columns, their definitions and defaults" all the columns are returned, but now constraints do not work.
-- + ISNULL(def.[definition] ,'')
Using this for testing, removed all the middle stuff. have looked at it but cannot figure out what is up. Any help is appreciated as I already wrote my tsql assuming this would work. Thanks.
declare @tblname varchar(128) = 'aaa'
declare @tablename varchar(128) = 'aaa'
declare @schemaname varchar(128) = 'dbo'
declare @vbCrLf char(2) = CHAR(13) + CHAR(10)
declare @table_id int = (select object_id from sys.tables where name = @tblname)
declare @finalsql varchar(max) = ''
declare @ddl varchar(max)
declare @input varchar(max)
declare @objecttypefound varchar(max)
declare @STRINGLEN int
SELECT @STRINGLEN = MAX(LEN(COLS.[name])) + 1
FROM sys.objects OBJS
INNER JOIN sys.columns COLS
ON OBJS.[object_id] = COLS.[object_id]
AND OBJS.[object_id] = @TABLE_ID;
--##############################################################################
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+
CASE
WHEN COLS.[is_computed] = 1
THEN @vbCrLf
+ QUOTENAME(COLS.[name])
+ ' '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ 'AS ' + ISNULL(CALC.definition,'')
+ CASE
WHEN CALC.is_persisted = 1
THEN ' PERSISTED'
ELSE ''
END
ELSE @vbCrLf
+ QUOTENAME(COLS.[name])
+ ' '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ UPPER(TYPE_NAME(COLS.[user_type_id]))
+ CASE
WHEN COLS.[default_object_id] = 0
THEN ' '
--ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')
--optional section in case NAMED default constraints are needed:
ELSE ' CONSTRAINT ' + quotename(def.name) + ' DEFAULT ' + ISNULL(def.[definition] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
END --iscomputed
+ ','
FROM sys.columns COLS
LEFT OUTER JOIN sys.default_constraints DEF
ON COLS.[default_object_id] = DEF.[object_id]
LEFT OUTER JOIN sys.computed_columns CALC
ON COLS.[object_id] = CALC.[object_id]
AND COLS.[column_id] = CALC.[column_id]
WHERE COLS.[object_id]=@TABLE_ID
ORDER BY COLS.[column_id]
select @FINALSQL
-
bgrossnickle are you using the original code fromt eh article, or the updated code from the discussions here?
over the years, we've made a lot of improvements; your example table scripted cleanly for me, in a case-insensitive collation
Updated yet again...same old links but new code.
--V314 03/30/2015
-- did i mention this scripts out temp tables too? sp_GetDDL #tmp
-- scripts a LOT more objects... a better replacement for sp_helptext i think.
- table,
- #temptable
- procedure,
- function,
- view
- trigger
- synonym (new!)
-- added ability to script synonyms
-- moved logic for REAL datatype to fix error when scripting real columns
-- added OmaCoders suggestion to script column extended properties as well.
-- added matt_slack suggestion to script schemaname as part of index portion of script.
-- minor script cleanup to use QUOTENAME insead of concatenating square brackets.
-- changed compatibility to 2008 and above only, now filtered idnexes with WHERE statmeents script correctly
-- foreign key tables and columns in script now quotenamed to accoutn for spaces in names; previously an error for Applciation ID instead of [Application ID]
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! -
Sorry, I should have mentioned this in the original post. I got the latest from http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt V314. I also tried the version that returns a table (DDLa??). Tried it on 2008R2 and 2012 SQL with same results. Seems that a common thread is that if I create a new database it works. But on my existing databases, that were probably created by restoring a DB that was created in SQL 2005 I only get the last column in the list of columns. I looked at compatibility, did a checkdb and don't know what else to look at. When I debug or use my test tsql in the previous post, I can see that the def.definition is the issue. Just referencing it (even if there is a ISNULL) causes the issue.
-
I'm guessing it has something to do with collation.
can you run these commands, and report what the values are?
with that, i could replicate the issue, ib et:
select databasepropertyex(db_name(),'collation')
select databasepropertyex('master','collation')
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! -
OK, think this has it working for all of my databases now. Put the string concatenation returned from ELSE into a SELECT - ELSE (SELECT .................. ). Got the idea from Dirty Secrets of the CASE Expression http://sqlperformance.com/2014/06/t-sql-queries/dirty-secrets-of-the-case-expression , which I did not fully read but the idea of putting it in a SELECT was something to try.
ELSE @vbCrLf
+ QUOTENAME(COLS.[name])
+ ' '
+ SPACE(@STRINGLEN - LEN(COLS.[name]))
+ UPPER(TYPE_NAME(COLS.[user_type_id]))
+ CASE
WHEN COLS.[default_object_id] = 0
THEN ''
--ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')
--optional section in case NAMED default constraints are needed:
ELSE (SELECT ' CONSTRAINT ' + quotename(ISNULL(def.name, '')) + ' DEFAULT ' + ISNULL(def.[definition] ,'') )
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
Viewing 15 posts - 76 through 90 (of 118 total)
You must be logged in to reply to this topic. Login to reply