Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Get DDL for any SQL 2005 table Expand / Collapse
Author
Message
Posted Sunday, July 12, 2009 6:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
Comments posted to this topic are about the item Get DDL for any SQL 2005 table

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #751783
Posted Monday, September 14, 2009 4:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #787297
Posted Tuesday, October 13, 2009 3:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 6, 2012 3:55 PM
Points: 2, 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.
Post #802426
Posted Tuesday, October 13, 2009 4:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #802456
Posted Tuesday, October 13, 2009 5:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 6, 2012 3:55 PM
Points: 2, 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
Post #802458
Posted Wednesday, October 14, 2009 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 16, 2009 9:26 AM
Points: 1, Visits: 4
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
Post #803117
Posted Wednesday, October 14, 2009 3:47 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Lowell,

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

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

CEWII
Post #803148
Posted Wednesday, October 14, 2009 3:58 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #803154
Posted Wednesday, October 14, 2009 4:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Well when I release my auditing and logging "project" on Codeplex you guys can take it apart..

CEWII
Post #803158
Posted Monday, October 19, 2009 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:44 AM
Points: 12,910, Visits: 32,015
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #805235
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse