April 7, 2010 at 12:04 pm
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
August 18, 2010 at 7:31 am
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?
August 18, 2010 at 7:46 am
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
August 18, 2010 at 9:28 am
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
August 18, 2010 at 9:34 am
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.
August 18, 2010 at 9:50 am
ahh kewl let me know how that works for you; that is one of the many reasons i made this proc( and a function that does the same thing)
i had previously done something very similar with DDL audit triggers, but as i remember it, inside the audit trigger, the changes had already occurred, so i couldn't get the PREVIOUS DDL for an object that changed, only the current.
that was minor in my opinion, since all i had to do was insert the sp_GetDDL of every current object as the basis for starting the audit trail. then it would be easy to compare the starting definition vs anything else the audit captured.
Lowell
August 19, 2010 at 1:11 pm
After 24Hrs of coding and searching for alternatives I almost gave it up but I finally get the DDL and previousversion DDL in a trigger working.
I have got it working quit fast, but could not get rid of the 3609 - transaction ended message from the trigger.
I tried the v309 code, but there are some errors in it. So I have manually configured your lines of new code in the previous version and extended it with an output into a variable.
This variable is used within the trigger to write the result in an audit table.
Currently I have got
* One trigger which fires after every DDL statement within a database
* The DDL procedure (which outputs a variable)
* Two tables, one with the complete lines of code in one attribute (the new DDL and the previous in two seperate attributes) and another table, whereas all the lines of DDL are split up by your splitter. This table references the basetable.
I am now working on a Cube and some reports to exploit these audit data in a friendly manner.
All the code is here below. Sorry for the missing comments, I will provide these at the beginning of next week.
The new SP_GETDDL
-- USAGE: exec sp_GetDDL YourTableName
-- or exec sp_GetDDL 'bob.example'
-- or exec sp_GetDDL '[schemaname].[tablename]'
--#############################################################################
-- copyright 2004-2009 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
-- http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V307.txt
-- 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
-- V-Vincent Wylenzek
--Extended V307 with the new lines of code out of V309, V309 did not work here
--Some other enhancements are:
--* fixing the number of linefeeds in the output
--* output everything to one single variable
--* export the generated code of the list into a detailed audittable
--NOTE: I ADDED A FIXED DB ZKS_AUDIT_DB WITH FIXED TABLES DATABASEAUDIT AND DATABASEAUDITDETAIL
--HERE ARE THE SCRIPTS FOR THE TABLES:
/*
CREATE TABLE [dbo].[DatabaseAudit](
[Audit_ID] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[LoginName] [sysname] NULL,
[EventType] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NOT NULL,
[TSQLCommand] [nvarchar](max) NULL,
[PreviousVersion] [nvarchar](max) NULL,
[XMLEventData] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[Audit_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[DatabaseAuditDetail](
[Audit_ID] [int] NOT NULL,
[Row_ID] [int] NOT NULL,
[ObjectName] [nvarchar](255) NULL,
[Row] [nvarchar](max) NULL,
CONSTRAINT [PK_DatabaseAuditDetail] PRIMARY KEY CLUSTERED
(
[Audit_ID] ASC,
[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
*/
-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced.The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#############################################################################
--if you are going to put this in MASTER, and want it to be able to query
--each database's sys.indexes, you MUST mark it as a system procedure:
--EXECUTE sp_ms_marksystemobject 'sp_GetDDL'
--#############################################################################
ALTER PROCEDURE [dbo].[sp_GetDDL] (@TBL NVARCHAR(255), @Result NVARCHAR(MAX) OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TBLNAME NVARCHAR(200),
@SCHEMANAME NVARCHAR(255),
@STRINGLEN INT,
@TABLE_ID INT,
@FINALSQL NVARCHAR(max),
@CONSTRAINTSQLS NVARCHAR(max),
@CHECKCONSTSQLS NVARCHAR(max),
@RULESCONSTSQLS NVARCHAR(max),
@FKSQLS NVARCHAR(max),
@TRIGGERSTATEMENT NVARCHAR(max),
@EXTENDEDPROPERTIES NVARCHAR(max),
@INDEXSQLS NVARCHAR(max),
@vbCrLf CHAR(2) ,
@input VARCHAR(max),
@Audit_ID INT
--##############################################################################
-- INITIALIZE
--##############################################################################
--SET @TBL = '[DBO].[WHATEVER1]'
--does the tablename contain a schema?
SET @vbCrLf = CHAR(13) + CHAR(10)
SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,
@TBLNAME = PARSENAME(@TBL,1)
insert into ZKS_AUDIT_DB.dbo.DatabaseAudit (Auditdate, SchemaName, ObjectName)
SELECT GETDATE(), @SCHEMANAME, @TBLNAME
set @Audit_ID = (select max(Audit_id) from ZKS_AUDIT_DB.dbo.DatabaseAudit where objectname = ''+ @TBLNAME+ '')
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) ;
--##############################################################################
-- 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 @Result = ISNULL((@FINALSQL),'-N/A-')
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 = '' + ISNULL(@TBLNAME,'') + '')
SET @Result = ISNULL((@FINALSQL),'-N/A-')
SET @input = @FINALSQL
--ten years worth of days from todays date:
;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ItemSplit(
ItemOrder,
Item
) as (
SELECT N,
SUBSTRING(@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
FROM Tally
WHERE N < DATALENGTH(@vbCrLf + @input)
--WHERE N < DATALENGTH(@vbCrLf + @input) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @input + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
--create table dbo.DatabaseAuditDetail (Audit_ID int, ObjectName nvarchar(255), Row nvarchar(max))
insert into ZKS_AUDIT_DB.dbo.DatabaseAuditDetail (Audit_ID, Row_ID, ObjectName, Row )
select @Audit_ID, row_number() over (order by ItemOrder) as ItemID, @TBLNAME, Item from ItemSplit
END
RETURN 0
END
--##############################################################################
-- Valid Table, Continue Processing
--##############################################################################
SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( '
--removed invalud cide here which potentially selected wrong table--thansk David Grifiths @SSC!
SELECT
@STRINGLEN = MAX(LEN(sys.columns.[name])) + 1
FROM sys.objects
INNER JOIN sys.columns
ON sys.objects.[object_id] = sys.columns.[object_id]
AND sys.objects.[object_id] = @TABLE_ID;
--##############################################################################
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+ CASE
WHEN sys.columns.[is_computed] = 1
THEN @vbCrLf
+ '['
+ UPPER(sys.columns.[name])
+ '] '
+ SPACE(@STRINGLEN - LEN(sys.columns.[name]))
+ 'AS ' + ISNULL(CALC.definition,'')
ELSE @vbCrLf
+ '['
+ UPPER(sys.columns.[name])
+ '] '
+ SPACE(@STRINGLEN - LEN(sys.columns.[name]))
+ UPPER(TYPE_NAME(sys.columns.[user_type_id]))
+ CASE
--IE NUMERIC(10,2)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('decimal','numeric')
THEN '('
+ CONVERT(NVARCHAR,sys.columns.[precision])
+ ','
+ CONVERT(NVARCHAR,sys.columns.[scale])
+ ') '
+ SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])
+ ','
+ CONVERT(NVARCHAR,sys.columns.[scale])))
+ SPACE(7)
+ SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0
THEN ''
ELSE ' IDENTITY('
+ CONVERT(NVARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(NVARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE FLOAT(53)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('float','real')
THEN
--addition: if 53, no need to specifically say (53), otherwise display it
CASE
WHEN sys.columns.[precision] = 53
THEN SPACE(11 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])))
+ 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(NVARCHAR,sys.columns.[precision])
+ ') '
+ SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])))
+ 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
--ie NVARCHAR(40)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('char','NVARCHAR')
THEN CASE
WHEN sys.columns.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(NVARCHAR,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(NVARCHAR,sys.columns.[max_length])
+ ') '
+ SPACE(6 - LEN(CONVERT(NVARCHAR,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
--ie NNVARCHAR(40)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('nchar','nNVARCHAR')
THEN CASE
WHEN sys.columns.[max_length] = -1
THEN '(max)'
+ SPACE(6 - LEN(CONVERT(NVARCHAR,(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(NVARCHAR,(sys.columns.[max_length]))
+ ') '
+ SPACE(6 - LEN(CONVERT(NVARCHAR,(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
--ie datetime
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('datetime','money','text','image')
THEN SPACE(18 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ ' '
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE INT
ELSE SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(NVARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(NVARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
+ SPACE(2)
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE
WHEN sys.columns.[default_object_id] = 0
THEN ''
--ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')
--optional section in case NAMED default cosntraints are needed:
ELSE @vbCrLf + 'CONSTRAINT [' + def.name + '] DEFAULT ' + ISNULL(def.[definition] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
--##############################################################################
-- COLLATE STATEMENTS
-- personally i do not like collation statements,
-- but included here to make it easy on those who do
--##############################################################################
/*
+ CASE
WHEN collation IS NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation
END
*/
END --iscomputed
+ ','
FROM sys.columns
LEFT OUTER JOIN sys.default_constraints DEF
on sys.columns.[default_object_id] = DEF.[object_id]
LEFT OUTER JOIN sys.computed_columns CALC
on sys.columns.[object_id] = CALC.[object_id]
and sys.columns.[column_id] = CALC.[column_id]
Where sys.columns.[object_id]=@TABLE_ID
ORDER BY sys.columns.[column_id]
--##############################################################################
--used for formatting the rest of the constraints:
--##############################################################################
SELECT
@STRINGLEN = MAX(LEN([name])) + 1
FROM sys.objects
--##############################################################################
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
DECLARE @Results TABLE (
[schema_id] int,
[schema_name] NVARCHAR(255),
[object_id] int,
[object_name] NVARCHAR(255),
[index_id] int,
[index_name] NVARCHAR(255),
[Rows] int,
[SizeMB] decimal(19,3),
[IndexDepth] int,
[type] int,
[type_desc] NVARCHAR(30),
[fill_factor] int,
[is_unique] int,
[is_primary_key] int ,
[is_unique_constraint] int,
[index_columns_key] NVARCHAR(max),
[index_columns_include] NVARCHAR(max))
INSERT INTO @Results
SELECT
sys.schemas.schema_id, sys.schemas.[name] AS schema_name,
sys.objects.[object_id], sys.objects.[name] AS object_name,
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]
JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE
sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME='' THEN sys.schemas.[name] ELSE @SCHEMANAME END
AND sys.objects.[name] LIKE CASE WHEN @TBLNAME='' THEN sys.objects.[name] ELSE @TBLNAME END
ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name]
--@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:
SET @CONSTRAINTSQLS = ''
SET @INDEXSQLS = ''
--##############################################################################
--constriants
--##############################################################################
SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS +
CASE
WHEN is_primary_key = 1 or is_unique = 1
THEN @vbCrLf
+ 'CONSTRAINT [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END
+ type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(NVARCHAR(30),fill_factor) ELSE '' END
ELSE ''
END + ','
from @RESULTS
where [type_desc] != 'HEAP'
AND is_primary_key = 1 or is_unique = 1
order by is_primary_key desc,is_unique desc
--##############################################################################
--indexes
--##############################################################################
SELECT @INDEXSQLS = @INDEXSQLS +
CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE INDEX [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ ' ON [' + [object_name] + ']'
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(NVARCHAR(30),fill_factor) ELSE '' END
END
from @RESULTS
where [type_desc] != 'HEAP'
AND is_primary_key = 0 AND is_unique = 0
order by is_primary_key desc,is_unique desc
IF @INDEXSQLS <> ''
SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS
--##############################################################################
--CHECK Constraints
--##############################################################################
SET @CHECKCONSTSQLS = ''
SELECT
@CHECKCONSTSQLS = @CHECKCONSTSQLS
+ @vbCrLf
+ ISNULL('CONSTRAINT [' + sys.objects.[name] + '] '
+ SPACE(@STRINGLEN - LEN(sys.objects.[name]))
+ ' CHECK ' + ISNULL(sys.check_constraints.definition,'')
+ ',','')
FROM sys.objects
INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id]
WHERE sys.objects.type = 'C'
AND sys.objects.parent_object_id = @TABLE_ID
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SET @FKSQLS = '' ;
SELECT
@FKSQLS=@FKSQLS
+ @vbCrLf
+ 'CONSTRAINT [' + OBJECT_NAME(constid) +']'
+ SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) ))
+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)
+ ') REFERENCES ' + OBJECT_NAME(rkeyid)
+'(' + COL_NAME(rkeyid,rkey) + '),'
from sysforeignkeys
WHERE fkeyid = @TABLE_ID
--##############################################################################
--RULES
--##############################################################################
SET @RULESCONSTSQLS = ''
SELECT
@RULESCONSTSQLS = @RULESCONSTSQLS
+ ISNULL(
@vbCrLf
+ 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(NVARCHAR(30),sys.objects.schema_id) + ' AND [name] = ''[' + object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf
+ sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf
+ 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + OBJECT_NAME(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO' ,'')
from sys.columns
inner join sys.objects
on sys.objects.[object_id] = sys.columns.[object_id]
inner join sys.sql_modules
on sys.columns.[rule_object_id] = sys.sql_modules.[object_id]
WHERE sys.columns.[rule_object_id] <> 0
and sys.columns.[object_id] = @TABLE_ID
--##############################################################################
--TRIGGERS
--##############################################################################
SET @TRIGGERSTATEMENT = ''
SELECT
@TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO'
FROM sys.sql_modules
WHERE [object_id] IN(SELECT
[object_id]
FROM sys.objects
WHERE type = 'TR'
AND [parent_object_id] = @TABLE_ID)
IF @TRIGGERSTATEMENT <> ''
SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT
--##############################################################################
--NEW SECTION QUERY ALL EXTENDED PROPERTIES
--##############################################################################
SET @EXTENDEDPROPERTIES = ''
SELECT @EXTENDEDPROPERTIES =
@EXTENDEDPROPERTIES + @vbCrLf +
'EXEC sys.sp_addextendedproperty
@name = N''' + [name] + ''', @value = N''' + REPLACE(convert(NVARCHAR(max),[value]),'''','''''') + ''',
@level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '],
@level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];'
--SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL);
IF @EXTENDEDPROPERTIES <> ''
SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES
--##############################################################################
--FINAL CLEANUP AND PRESENTATION
--##############################################################################
--at this point, there is a trailing comma, or it blank
SELECT
@FINALSQL = @FINALSQL
+ @CONSTRAINTSQLS
+ @CHECKCONSTSQLS
+ @FKSQLS
--note that this trims the trailing comma from the end of the statements
SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;
SET @FINALSQL = @FINALSQL + ')' + @vbCrLf ;
--Set @FINALSQL = ( @FINALSQL
--+ @INDEXSQLS
--+ @RULESCONSTSQLS
--+ @TRIGGERSTATEMENT
--+ @EXTENDEDPROPERTIES
--)
----print @Result
--note that this trims the trailing comma from the end of the statements
SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;
SET @Result = (@FINALSQL)
SET @input = @FINALSQL
+ @INDEXSQLS
+ @RULESCONSTSQLS
+ @TRIGGERSTATEMENT
+ @EXTENDEDPROPERTIES
--ten years worth of days from todays date:
;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows
E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows
E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows
E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows
--E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,
Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),
ItemSplit(
ItemOrder,
Item
) as (
SELECT N,
SUBSTRING(@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))
FROM Tally
WHERE N < DATALENGTH(@vbCrLf + @input)
--WHERE N < DATALENGTH(@vbCrLf + @input) -- REMOVED added @vbCrLf
AND SUBSTRING(@vbCrLf + @input + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter
)
--create table dbo.DatabaseAuditDetail (Audit_ID int, ObjectName nvarchar(255), Row nvarchar(max))
insert into ZKS_AUDIT_DB.dbo.DatabaseAuditDetail (Audit_ID, Row_ID, ObjectName, Row )
select @Audit_ID, row_number() over (order by ItemOrder) as Row_ID, @TBLNAME, Item from ItemSplit
END
The trigger (without comments sorry no time)
create trigger DatabaseAuditTrigger
on database
for ddl_database_level_events
as
set nocount on;
declare @Cmd nvarchar(max)
declare @EventData xml
declare @Input nvarchar (255)
declare @Object nvarchar(200)
declare @PreviousVersion nvarchar(max)
declare @Schema nvarchar(55)
declare @Audit_ID int
--Create audittabel (if not exists)
IF NOT EXISTS (select 1 from ZKS_AUDIT_DB.sys.objects where name = 'DatabaseAudit')
BEGIN
create table [ZKS_AUDIT_DB].[dbo].[DatabaseAudit](
[Audit_ID] int identity(1,1) primary key,
[AuditDate] [datetime] not null,
[LoginName] [sysname] null,
[EventType] [sysname] null,
[SchemaName] [sysname] null,
[ObjectName] [sysname] not null,
[TSQLCommand] [nvarchar](max) null,
[PreviousVersion] [nvarchar](max) null,
[XMLEventData] [xml] null
) ON [PRIMARY]
END
--Get the DDL that triggered the trigger
set @EventData = eventdata()
set @PreviousVersion = ''
set @Schema = (select isnull
( @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@EventData.value('data(/EVENT_INSTANCE/DefaultSchema)[1]', 'sysname')
)
)
set @Object = (select @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'))
set @Input = @Schema + '.' + @Object
set @cmd = (Select @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)'))
--issue a rollback, so the previous situation is maintained
ROLLBACK
--instantiate SP_GETDDL to retrieve the previous DDL and write it to a variable @cmd
exec Sp_Getddl @Input, @PreviousVersion output
exec(@cmd)--execute the DDL that triggered this trigger again (although the rollback has occureD)
set @Audit_ID = (select max(audit_id) from ZKS_AUDIT_DB.DBO.DatabaseAudit where ObjectName = @Object)
--write the auditrecord
UPDATE ZKS_AUDIT_DB.dbo.DatabaseAudit SET LoginName = @EventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
TSQLCommand = @cmd,
PreviousVersion = @PreviousVersion,
XMLEventData = @EventData
WHERE Audit_id = @Audit_ID
--start a new transaction to disable the 3609 message
begin tran
The tables
CREATE TABLE [dbo].[DatabaseAudit](
[Audit_ID] [int] IDENTITY(1,1) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[LoginName] [sysname] NULL,
[EventType] [sysname] NULL,
[SchemaName] [sysname] NULL,
[ObjectName] [sysname] NOT NULL,
[TSQLCommand] [nvarchar](max) NULL,
[PreviousVersion] [nvarchar](max) NULL,
[XMLEventData] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[Audit_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[DatabaseAuditDetail](
[Audit_ID] [int] NOT NULL,
[Row_ID] [int] NOT NULL,
[ObjectName] [nvarchar](255) NULL,
[Row] [nvarchar](max) NULL,
CONSTRAINT [PK_DatabaseAuditDetail] PRIMARY KEY CLUSTERED
(
[Audit_ID] ASC,
[Row_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
August 19, 2010 at 3:17 pm
looks like mine is similar to yours; just different fields that i'm capturing.
here's what i see as the differences:
I'm using sp_GetDDL not sp_GetDDLa, which returns multiple rows. i am using v309 with no problems at all. if you can identify the errors you found, i'd appreciate it...you did mark it a s a system proc in master, right? my code is the same i wrote when my proc was version 306.
i catch a few more columns of data.
i'm using the audit table as a table in master, so i can simply deploy or not deploy my suite of triggers into a database, and the audit is not tracked inside the database i'm auditing.
here's my code:
use master
CREATE TABLE [dbo].[DDLEVENTLOG] (
[DATABASENAME] SYSNAME NOT NULL,
[EVENTDATE] DATETIME NULL,
[USERNAME] SYSNAME NULL DEFAULT (getdate()),
[SYSTEMUSER] VARCHAR(128) NULL,
[CURRENTUSER] VARCHAR(128) NULL,
[ORIGINALUSER] VARCHAR(128) NULL,
[HOSTNAME] VARCHAR(128) NULL,
[APPLICATIONNAME] VARCHAR(128) NULL,
[SCHEMANAME] SYSNAME NULL,
[OBJECTNAME] SYSNAME NULL,
[OBJECTTYPE] SYSNAME NULL,
[EVENTTYPE] VARCHAR(128) NULL,
[EVENTDATA] XML NULL,
[COMMANDTEXT] VARCHAR(max) NULL,
[OBJECTDEFINITION] VARCHAR(max) NULL)
GO
use SandBox
GO
ALTER TRIGGER [ReturnPREventData]
ON DATABASE
WITH EXECUTE AS 'dbo'
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,
CREATE_FUNCTION, DROP_FUNCTION, ALTER_FUNCTION,
CREATE_VIEW, DROP_VIEW, ALTER_VIEW,
CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_TRIGGER, DROP_TRIGGER, ALTER_TRIGGER
AS
BEGIN
SET NOCOUNT ON
declare @ObjectDef table(definition varchar(max))
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--get the object definition
SET @NAMEFORDEFINITION = '[' + @SCHEMANAME + '].[' + @OBJECTNAME + ']'
INSERT INTO @ObjectDef(definition)
EXEC sp_getDDL @NAMEFORDEFINITION
--now save the audit info
INSERT [master].[dbo].[DDLEventLog] (DATABASENAME,EVENTDATE,USERNAME,SYSTEMUSER,CURRENTUSER,ORIGINALUSER,HOSTNAME,APPLICATIONNAME,SCHEMANAME,OBJECTNAME,OBJECTTYPE,EVENTTYPE,EVENTDATA,COMMANDTEXT,OBJECTDEFINITION)
SELECT
@DATABASENAME,
@EVENTDATE,
@USERNAME,
@SYSTEMUSER,
@CURRENTUSER,
@ORIGINALUSER,
@HOSTNAME,
@APPLICATIONNAME,
@SCHEMANAME,
@OBJECTNAME,
@OBJECTTYPE,
@EVENTTYPE,
@eventData,
@COMMANDTEXT,
definition
FROM @ObjectDef
END --DB TRIGGER
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE
GO
Lowell
August 21, 2010 at 5:34 am
The 309 version returned no result, it did not give an error, but it returned a blank string. I did not have the time to debug it, so I used the previous version, I thought it was v307.
The main difference between your and mine trigger, is that mines execute a rollback, so you can get the DDL of the previous situation as well. Before I execute the rollback, I save the executed DDL command that triggered the trigger in a variable. The great thing about the rollback is that the values of variables are not rollbacked. So after the rollback I execute the DDL from this variable and log the DDL in the same table and same row.
Now it is possible to save both the new and the old DDL in te same row.
The detail table contains the new and old DDL and each line of code implicates a new record (actually this is your splitter functionality).
September 28, 2010 at 8:56 am
Hi
About the collate section :
+ CASE
WHEN collation IS NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation
END
I'm using SQL Server 2008 and I have a bug trying to add this part :
so I tried to add it in the NVARCHAR and VARCHAR section right before the [is_nullable] part :
CREATE PROCEDURE [dbo].[sp_GetDDL]
@TBL VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TBLNAME VARCHAR(200),
@SCHEMANAME VARCHAR(255),
@STRINGLEN INT,
@TABLE_ID INT,
@FINALSQL VARCHAR(max),
@CONSTRAINTSQLS VARCHAR(max),
@CHECKCONSTSQLS VARCHAR(max),
@RULESCONSTSQLS VARCHAR(max),
@FKSQLS VARCHAR(max),
@TRIGGERSTATEMENT VARCHAR(max),
@EXTENDEDPROPERTIES VARCHAR(max),
@INDEXSQLS VARCHAR(max),
@vbCrLf CHAR(2)
--##############################################################################
-- INITIALIZE
--##############################################################################
--SET @TBL = '[DBO].[WHATEVER1]'
--does the tablename contain a schema?
SET @vbCrLf = CHAR(13) + CHAR(10)
SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,
@TBLNAME = PARSENAME(@TBL,1)
SELECT
@TABLE_ID = [object_id]
FROM sys.objects
WHERE [type] = 'U'
AND [name] <> 'dtproperties'
AND [name] = @TBLNAME
AND [schema_id] = schema_id(@SCHEMANAME) ;
--##############################################################################
-- Check If TableName is Valid
--##############################################################################
IF ISNULL(@TABLE_ID,0) = 0
BEGIN
SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']'
SELECT @FINALSQL;
RETURN 0
END
--##############################################################################
-- Valid Table, Continue Processing
--##############################################################################
SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( '
SELECT @TABLE_ID = OBJECT_ID(@TBLNAME)
SELECT
@STRINGLEN = MAX(LEN(sys.columns.[name])) + 1
FROM sys.objects
INNER JOIN sys.columns
ON sys.objects.[object_id] = sys.columns.[object_id]
AND sys.objects.[object_id] = @TABLE_ID;
--##############################################################################
--Get the columns, their definitions and defaults.
--##############################################################################
SELECT
@FINALSQL = @FINALSQL
+ CASE
WHEN sys.columns.[is_computed] = 1
THEN @vbCrLf
+ '['
+ UPPER(sys.columns.[name])
+ '] '
+ SPACE(@STRINGLEN - LEN(sys.columns.[name]))
+ 'AS ' + ISNULL(CALC.definition,'')
ELSE @vbCrLf
+ '['
+ UPPER(sys.columns.[name])
+ '] '
+ SPACE(@STRINGLEN - LEN(sys.columns.[name]))
+ UPPER(TYPE_NAME(sys.columns.[user_type_id]))
+ CASE
--IE NUMERIC(10,2)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('decimal','numeric')
THEN '('
+ CONVERT(VARCHAR,sys.columns.[precision])
+ ','
+ CONVERT(VARCHAR,sys.columns.[scale])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision])
+ ','
+ CONVERT(VARCHAR,sys.columns.[scale])))
+ SPACE(2)
-- + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE FLOAT(53)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('float','real')
THEN
--addition: if 53, no need to specifically say (53), otherwise display it
CASE
WHEN sys.columns.[precision] = 53
THEN SPACE(11 - LEN(CONVERT(VARCHAR,sys.columns.[precision])))
+ 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.[precision])
+ ') '
+ SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision])))
+ 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
--ie VARCHAR(40)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('char','varchar')
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.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation_name
END
+ 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.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation_name
END
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie NVARCHAR(40)
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('nchar','nvarchar')
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.collation_name IS NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation_name
END
+ 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.collation_name IS NOT NULL
THEN ''
ELSE ' COLLATE ' + sys.columns.collation_name
END
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
--ie datetime
WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('datetime','money','text','image')
THEN SPACE(18 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ ' '
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
--IE INT
ELSE SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))
+ CASE
WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0
THEN ' '
ELSE ' IDENTITY('
+ CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )
+ ','
+ CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )
+ ')'
END
+ SPACE(2)
+ CASE
WHEN sys.columns.[is_nullable] = 0
THEN ' NOT NULL'
ELSE ' NULL'
END
END
+ CASE
WHEN sys.columns.[default_object_id] = 0
THEN ''
--ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')
--optional section in case NAMED default cosntraints are needed:
ELSE @vbCrLf + 'CONSTRAINT [' + def.name + '] DEFAULT ' + ISNULL(def.[definition] ,'')
--i thought it needed to be handled differently! NOT!
END --CASE cdefault
END --iscomputed
+ ','
FROM sys.columns
LEFT OUTER JOIN sys.default_constraints DEF
on sys.columns.[default_object_id] = DEF.[object_id]
LEFT OUTER JOIN sys.computed_columns CALC
on sys.columns.[object_id] = CALC.[object_id]
and sys.columns.[column_id] = CALC.[column_id]
Where sys.columns.[object_id]=@TABLE_ID
ORDER BY sys.columns.[column_id]
--##############################################################################
--used for formatting the rest of the constraints:
--##############################################################################
SELECT
@STRINGLEN = MAX(LEN([name])) + 1
FROM sys.objects
--##############################################################################
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
DECLARE @Results TABLE (
[schema_id] int,
[schema_name] varchar(255),
[object_id] int,
[object_name] varchar(255),
[index_id] int,
[index_name] varchar(255),
[Rows] int,
[SizeMB] decimal(19,3),
[IndexDepth] int,
[type] int,
[type_desc] varchar(30),
[fill_factor] int,
[is_unique] int,
[is_primary_key] int ,
[is_unique_constraint] int,
[index_columns_key] varchar(max),
[index_columns_include] varchar(max))
INSERT INTO @Results
SELECT
sys.schemas.schema_id, sys.schemas.[name] AS schema_name,
sys.objects.[object_id], sys.objects.[name] AS object_name,
sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,
sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,
sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include
FROM
sys.objects
JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]
JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats
GROUP BY [object_id], index_id
) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id
CROSS APPLY (
SELECT
LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
FROM
(
SELECT
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=0
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY key_ordinal
FOR XML PATH('')
) AS index_columns_key,
(
SELECT sys.columns.[name] + ',' + ' '
FROM
sys.index_columns
JOIN sys.columns ON
sys.index_columns.column_id=sys.columns.column_id
AND sys.index_columns.[object_id]=sys.columns.[object_id]
WHERE
sys.index_columns.is_included_column=1
AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id
ORDER BY index_column_id
FOR XML PATH('')
) AS index_columns_include
) AS Index_Columns
) AS Index_Columns
WHERE
sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME='' THEN sys.schemas.[name] ELSE @SCHEMANAME END
AND sys.objects.[name] LIKE CASE WHEN @TBLNAME='' THEN sys.objects.[name] ELSE @TBLNAME END
ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name]
--@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:
SET @CONSTRAINTSQLS = ''
SET @INDEXSQLS = ''
--##############################################################################
--constriants
--##############################################################################
SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS +
CASE
WHEN is_primary_key = 1 or is_unique = 1
THEN @vbCrLf
+ 'CONSTRAINT [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END
+ type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END
ELSE ''
END + ','
from @RESULTS
where [type_desc] != 'HEAP'
AND is_primary_key = 1 or is_unique = 1
order by is_primary_key desc,is_unique desc
--##############################################################################
--indexes
--##############################################################################
SELECT @INDEXSQLS = @INDEXSQLS +
CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE INDEX [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ ' ON [' + [object_name] + ']'
+ ' (' + index_columns_key + ')'
+ CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END
+ CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END
END
from @RESULTS
where [type_desc] != 'HEAP'
AND is_primary_key = 0 AND is_unique = 0
order by is_primary_key desc,is_unique desc
IF @INDEXSQLS <> ''
SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS
--##############################################################################
--CHECK Constraints
--##############################################################################
SET @CHECKCONSTSQLS = ''
SELECT
@CHECKCONSTSQLS = @CHECKCONSTSQLS
+ @vbCrLf
+ ISNULL('CONSTRAINT [' + sys.objects.[name] + '] '
+ SPACE(@STRINGLEN - LEN(sys.objects.[name]))
+ ' CHECK ' + ISNULL(sys.check_constraints.definition,'')
+ ',','')
FROM sys.objects
INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id]
WHERE sys.objects.type = 'C'
AND sys.objects.parent_object_id = @TABLE_ID
--##############################################################################
--FOREIGN KEYS
--##############################################################################
SET @FKSQLS = '' ;
SELECT
@FKSQLS=@FKSQLS
+ @vbCrLf
+ 'CONSTRAINT [' + OBJECT_NAME(constid) +']'
+ SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) ))
+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)
+ ') REFERENCES ' + OBJECT_NAME(rkeyid)
+'(' + COL_NAME(rkeyid,rkey) + '),'
from sysforeignkeys
WHERE fkeyid = @TABLE_ID
--##############################################################################
--RULES
--##############################################################################
SET @RULESCONSTSQLS = ''
SELECT
@RULESCONSTSQLS = @RULESCONSTSQLS
+ ISNULL(
@vbCrLf
+ 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(varchar(30),sys.objects.schema_id) + ' AND [name] = ''[' + object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf
+ sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf
+ 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + OBJECT_NAME(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO' ,'')
from sys.columns
inner join sys.objects
on sys.objects.[object_id] = sys.columns.[object_id]
inner join sys.sql_modules
on sys.columns.[rule_object_id] = sys.sql_modules.[object_id]
WHERE sys.columns.[rule_object_id] <> 0
and sys.columns.[object_id] = @TABLE_ID
--##############################################################################
--TRIGGERS
--##############################################################################
SET @TRIGGERSTATEMENT = ''
SELECT
@TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO'
FROM sys.sql_modules
WHERE [object_id] IN(SELECT
[object_id]
FROM sys.objects
WHERE type = 'TR'
AND [parent_object_id] = @TABLE_ID)
IF @TRIGGERSTATEMENT <> ''
SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT
--##############################################################################
--NEW SECTION QUERY ALL EXTENDED PROPERTIES
--##############################################################################
SET @EXTENDEDPROPERTIES = ''
SELECT @EXTENDEDPROPERTIES =
@EXTENDEDPROPERTIES + @vbCrLf +
'EXEC sys.sp_addextendedproperty
@name = N''' + [name] + ''', @value = N''' + REPLACE(convert(varchar(max),[value]),'''','''''') + ''',
@level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '],
@level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];'
--SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL);
IF @EXTENDEDPROPERTIES <> ''
SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES
--##############################################################################
--FINAL CLEANUP AND PRESENTATION
--##############################################################################
--at this point, there is a trailing comma, or it blank
SELECT
@FINALSQL = @FINALSQL
+ @CONSTRAINTSQLS
+ @CHECKCONSTSQLS
+ @FKSQLS
--note that this trims the trailing comma from the end of the statements
SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;
SET @FINALSQL = @FINALSQL + ')' + @vbCrLf ;
SELECT @FINALSQL
+ @INDEXSQLS
+ @RULESCONSTSQLS
+ @TRIGGERSTATEMENT
+ @EXTENDEDPROPERTIES
END
GO
Great work Stormrage
and thx
MBS.
September 28, 2010 at 9:38 am
thank you very much for the feedback and correction;
at one point the original script was pointing at the old compatibility tables like syscolumns; that badly named column is a legacy of that.
i migrated it over to use the newer views, as that seemed the right thing to do;
hope this is working out for you!
Lowell
February 25, 2011 at 10:17 am
Hello Lowell -
First - thank you for always helping me out with my questions in the Newbies forum. I came across this work of yours today, and forgive the very neophyte-like question here, but how is this different than simply doing a CREATE to from Right-Clicking one of my existing tables? I know there has to be more to this than that - I have followed the thread here and see the revisions of your work, and everyones comments, but after having created the proc on my end, run it against one of my tables, I am find no real differences in your proc and my results from my previous description.
I must be missing something. Please forgive the question to understand here, because I definitely mean no insult by asking it (I want to use this code to recreate my tables, but need to understand what it's going to do for me first).
Thank you!
February 25, 2011 at 10:28 am
well, i've got two variations of this...one returns the definition as a single varchar(max), and the other splits the same varchar(max) on CrLf so it's multiple rows.
one purpose is standardization...developers in my shop have different styles for the tables that they create, and that's no problem for me...but when it comes to the final deployment script, the well formatted well spaced version of the DDL this proc creates adds to a better presentation.
another purpose of it is nothing more than SQL Server does not provide the ability to get this data via a TSQL statement natively.
you have use the GUI, which in turn uses SMO. 7 plus years ago there was a post that asked if it could be done, and that got my mind working.
in my case, I'm using it for a few different things....DDL auditing mostly...i can capture the old and new definitions of a table in an audit table.
but i can also script a database's complete definition via TSQL, instead of an outside process; that lead me to make my own "schema checker", where i can compare the definition of one table to another....helps me a lot with remote client databases,
another adaptation i have takes that table definition and makes the ORACLE database equivalent, based on my SQL table...that is a huge timesaver in my shop, where we support An app that connects to SQL or Oracle.
A lot of it, at first, was to prove it could be done; just one more tool in the toolbox.
Lowell
February 25, 2011 at 11:15 am
Also because mine is purtier!
one of the main reasons i did this was formatting, and removing/pruning items that are optional in a table script, so the don't need to be defined explicitly if you are taking the defaults (like CREATE TABLE (...) ON PRIMARY
compare these two:
--From SSMS
CREATE TABLE [dbo].[ICONINFO](
[RESULTSID] [int] IDENTITY(1,1) NOT NULL,
[FILEFULLNAME] [varchar](1024) NULL,
[FILEPATH] [varchar](1024) NULL,
[FILENAME] [varchar](100) NULL,
[FILEEXTENSION] [varchar](10) NULL,
[FILESIZE] [int] NULL,
[CHECKSUM] [varchar](50) NULL,
[CREATIONDATE] [datetime] NULL,
[RESULTSDESCRIPTION] [varchar](100) NULL,
CONSTRAINT [PK__IconInfo] PRIMARY KEY CLUSTERED
(
[RESULTSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--From sp_getddla
CREATE TABLE [dbo].[ICONINFO] (
[RESULTSID] INT IDENTITY(1,1) NOT NULL,
[FILEFULLNAME] VARCHAR(1024) NULL,
[FILEPATH] VARCHAR(1024) NULL,
[FILENAME] VARCHAR(100) NULL,
[FILEEXTENSION] VARCHAR(10) NULL,
[FILESIZE] INT NULL,
[CHECKSUM] VARCHAR(50) NULL,
[CREATIONDATE] DATETIME NULL,
[RESULTSDESCRIPTION] VARCHAR(100) NULL,
CONSTRAINT [PK__IconInfo] PRIMARY KEY CLUSTERED (RESULTSID))
Lowell
October 17, 2011 at 11:24 am
Hey Lowell,
I'm sure you're not expecting a reply so long since you last updated, but here it is. This SP is absolutely fantastic - a work of art - but I seem to have found a small hiccup in it. I have some triggers which have comments in them, commented out not using /* and */, but the old -- instead. Since your result returns one potentially very very long row, everything beyond the first doube-dash is then a comment. Any ideas how we can get around this?
Thanks in advance!:-D
EDIT: I just realised your GetDDLa might just take care of exactly that, but the link to 309a seems to be dead 🙁
Viewing 15 posts - 16 through 30 (of 118 total)
You must be logged in to reply to this topic. Login to reply