Get DDL for any SQL 2005 table

Question

Get DDL for any SQL 2005 table

Lowell

SSC Guru

Points: 323518
More actions
July 12, 2009 at 6:15 pm

#208099

Comments posted to this topic are about the item Get DDL for any SQL 2005 table
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!

Viewing 15 posts - 1 through 15 (of 127 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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))

GO

EXEC sys.sp_addextendedproperty

@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];

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!

Nathan Kilgore SSC Enthusiast Points: 164 More actions · Answer 2

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

Thank you.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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 ,

blahname sysname)

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":

is_replicated

is_non_sql_subscribed

is_merge_published

is_dts_replicated

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.

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!

Nathan Kilgore SSC Enthusiast Points: 164 More actions · Answer 4

Lowell,

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.

Nathan

sarodude SSC Journeyman Points: 81 More actions · Answer 5

Lowell-

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.

-Saro

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 6

Lowell,

COLUMNPROPERTY('IsIdNotForRepl') should return 0/1/NULL

I'd look over COLUMNPROPERTY, it has a bunch of useful information..

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 7

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.

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!

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

Well when I release my auditing and logging "project" on Codeplex you guys can take it apart..

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 9

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.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David In BC SSC-Addicted Points: 466 More actions · Answer 10

There's a bug in this script at approximately line 82 - details below.

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

SELECT

@TABLE_ID = [object_id]

FROM sys.objects

WHERE [type] IN ('S','U','V')

AND [name] <> 'dtproperties'

AND [name] = @TBLNAME

AND [schema_id] = schema_id(@SCHEMANAME) ;

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

-- Valid Table, Continue Processing

SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( '

SELECT @TABLE_ID = OBJECT_ID(@TBLNAME)

David

Lowell SSC Guru Points: 323518 More actions · Answer 11

wow excellent catch David! I rarely work under multiple schemas, and certainly missed this!

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

here is the updated version: sp_GetDDL2005_V307

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

Thanks David!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David In BC SSC-Addicted Points: 466 More actions · Answer 12

No problem, and thanks for the excellent script.

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

David

Lowell SSC Guru Points: 323518 More actions · Answer 13

yeah i've used this code for a "schema checker" which compares a database to various xml docs;

based on an internal table, if your db is marked "version 4" or "version4.1", etc , it uses the appropriate xml doc from a website to check for objects;anything missing in the target database gets the script that was generated from this proc; works well for me;

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

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Thanks Lowell for a superb script.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events