Comments posted to this topic are about the item Get DDL for any SQL 2005 table
sp_getDDL returns the complete CREATE TABLE command in TSQL; I've been using it in a DDL audit trigger, and also using it more often then sp_help.
Could some of you folks please test drive my contribution a bit? a extrapair of eyes would help flesh out any mistakes or sections that need improvement.
I updated this procedure so that it additionally returns extended properties about the table in question.
here's some sample output:
CREATE TABLE [dbo].[TBSTATE] (
[STATETBLKEY] INT NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATECODE] CHAR(2) NOT NULL,
[STATENAME] VARCHAR(50) NOT NULL,
[FIPS] CHAR(2) NULL,
CONSTRAINT [PK__TBSTATE__17A421EC] PRIMARY KEY CLUSTERED (STATETBLKEY),
CONSTRAINT [STATECODEUNIQUE] UNIQUE NONCLUSTERED (STATECODE))
@name = N'FIPS', @value = N'the FIPS code is a two digit numeric-only varchar with preceeding zeros assigned by the government',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'TABLE', @level1name = [TBSTATE];
Let me first say that this is a wonderful bit of code.
I am looking for fixes to any of the following issues I have encountered:
1. Identity issue "not for replication" is missing
2. Identity if the type is anything other than an Integer no identity is created
3. When column type is user defined or SYSNAMES they end up as nvarchar
Nathan thanks for the feedback; having a different pair of eyes helps so much shaking down a proc like this.
#2 and #3 on the list of the things you identified I could correct immediately: all the int types were working(tinyint/smallint/bigint), and i added decimal and numeric; i think that is the complete list of allowable identity column types, right?
#3 was selecting a different column: user_type_id immediately fixed the type_name issue.
you can download the latest version here
for item #1, i know where the command *should* go in the script, but i don't know how to identify the field:
use this code as an example:
create table blah(
blahid decimal(18,0) identity(1,1) NOT FOR REPLICATION primary key ,
select * from sys.columns where object_name(object_id)='blah'
exec sp_getddl blah
drop table blah
if you look at the columns in sys.columns, I see several candidates for identifying "NOT FOR REPLICATION":
I don't know IF those columns are used to identify that property or not...never do replication at my shop.
if you have a replicated database, could you tell me which might be the one?
since I never replicate, i don't see the advantage of adding that optional command; i similarly skipped saying what file group and some other things related to the table as well.
Thank you very much for the fast reply. It is funny I went ahead and coded it for the other numeric data types that can be used as Identity and our code is very near identical. I am going to look at the replicated database that I have and do a comparison to come up with the correct field.
Thank you again for the fast response.
V305 appears to have a bug which prevents compilation at line 121.
+ SPACE(2) + WHEN sys.columns.[is_nullable] = 0
It looks like that section was the subject of revision compared with V304.
(edit) Forgot to mention that removing "+ WHEN sys.columns.[is_nullable] = 0" seems to allow compilation.
COLUMNPROPERTY('IsIdNotForRepl') should return 0/1/NULL
I'd look over COLUMNPROPERTY, it has a bunch of useful information..
Elliot thanks, I'm looking at it now, if i get it to work i'll add another version;
sarodude, thanks! i had it working, then was trying to find the right column in sys.columns, added some sample code, and then saved it....my fault.
i fixed it again so it's compiling correctly.
thanks everyone for input on my mini-project.
Well when I release my auditing and logging "project" on Codeplex you guys can take it apart..
ok I was playing with this today and found that i somehow broke the code for an computed columns;
I fixed and tested the solution, and here we go again:
here is the updated version: sp_GetDDL2005_V306 fixes that, and also tweaks some alignment issues for when a column is defined as a decimal.
the join for sys.computed_columns was completely missing, as well as the .definition...i know it worked in the cursor version.
Thanks for making me look at this again, guys.
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:
@TABLE_ID = [object_id]
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)
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.
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.
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.
Thanks Lowell for a superb script.
I have given a name to my pain...MCM SQL Server, MVP
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 116 total)