SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get DDL for any SQL 2005 table


Get DDL for any SQL 2005 table

Author
Message
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70250 Visits: 40923
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!
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70250 Visits: 40923
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
Nathan Kilgore
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 77
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
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70250 Visits: 40923
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
Nathan Kilgore
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 77
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
sarodude
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23512 Visits: 5314
Lowell,

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

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

CEWII
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70250 Visits: 40923
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
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23512 Visits: 5314
Well when I release my auditing and logging "project" on Codeplex you guys can take it apart..

CEWII
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70250 Visits: 40923
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search