Get DDL for any SQL 2005 table

Question

Get DDL for any SQL 2005 table

Viewing 15 posts - 31 through 45 (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

kbleeker (10/17/2011)
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

it's been a while, but i really use a variation of this every single day;

When i first wrote this, i was in love with varchar(max) strings;

so you can sometimes run into trouble when a trigger was written with just CR separators CHAR(13) instead of CrLf CHAR(13) + CHAR(10), which i suspect is your specific issue.

if you want to stick with the varchar(max) version, you might need to add a REPLACE(CHAR(13),CHAR(13) + CHAR(10) to the trigger results portion.

--edit--

actually, in the varchar version below, on line 83, change

SET @vbCrLf = CHAR(13) + CHAR(10)

to

SET @vbCrLf = CHAR(13)

if you want to switch to a table being returned, the latest version i have is here:

varchar max version: sp_GetDDL2008_V310a.txt

Table max version: sp_GetDDLa2008_V310a.txt

sp_GetDDLa2008_V310a.txt

that version works for any object...not just tables, and will script out any temp table you have scope to as well...same old syntax:

EXEC sp_GetDDLa '[dbo].[Tablename]'

--or

EXEC sp_GetDDLa #Tablename

--or

EXEC sp_GetDDLa ProcedureOrFunctionName

i'll see if i can create something with the issue you identified and add it to the original varchar version.

the tough part is getting the same test scenario.

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 2

Wow man!! I can't get over that response time! Thanks a stack, the table method will fit me to a T - again, just to say thanks, I'm completely blown away by the enormity and intricacy of this project and how dedicated you've clearly been to it.

As you said, another tool in the toolbox, but it'll be one of my most useful wrenches, if I can predict worth a damn.

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

Ah, you know whats funny Lowell, I mentioned releasing my monitoring and logging project to codeplex way up in the chain, I am only now about ready to actually release it.. Re-wrote it a couple times along the way..

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 4

Elliott Whitlow (10/17/2011)
Ah, you know whats funny Lowell, I mentioned releasing my monitoring and logging project to codeplex way up in the chain, I am only now about ready to actually release it.. Re-wrote it a couple times along the way..
CEWII

Lol, your CLR project got me to build some basic CLR ExportToCSV/TSV/HTML/more stuff that i'm stest driving around now; it's funny how one gets a push off of others!

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 5

Are you the Lowell who wrote the docs for it from the discussion on there? I just added a tweaked up version as a docx file last week.

CEWII

Lowell SSC Guru Points: 323518 More actions · Answer 6

Elliott Whitlow (10/17/2011)
Are you the Lowell who wrote the docs for it from the discussion on there? I just added a tweaked up version as a docx file last week.
CEWII

yes, one in the same; you pointed me to it from a different post here, and I really enjoyed fiddling with it; I wanted to help out if i could.

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 7

Take a look at the doc, most of it was based on your work and I gave you credit for your assistance. And if you see something missing or that I could implement better I would love to hear about it. I don't believe for a second that everything is "just right". I've been using the pre versions of that assembly since '08, that version was just prettied up so I wouldn't embarass myself when I released it.

I've got a config management and monitoring project that I have been working on, the data collection portion is mostly done. I just finished up the major cluster collection parts. Man that is kind of tough.. I wanted to be able to discover not just the nodes that a particular instance can run on (which SQL will tell me), but the name of the actual cluster as well as ALL the nodes in the cluster and which disks are shared disks as opposed to "local" only. WMI can be fun.

CEWII

kbleeker SSC Veteran Points: 267 More actions · Answer 8

ARGH! This is why I hate old databases - in the db I'm testing the script on, I have many many tables with indexes on single columns, so this gets generated:

CREATE INDEX

ON [ACCESS] (CODE,) WITH FILLFACTOR = 90

I'm sure you can see the syntax error already...any ideas?

Lowell SSC Guru Points: 323518 More actions · Answer 9

CREATE INDEX [ CODE ]

ON [ACCESS] (CODE,) WITH FILLFACTOR = 90

where are you getting that? in the results from sp_GetDDLa? when i script a table that happens to have indexes, at the bottom i get well formatted index definitions, i think:

CREATE INDEX [ACTINDEX] ON [GMACT] (ACTNBR) WITH FILLFACTOR = 90

CREATE INDEX [ACTNAMEINDEX] ON [GMACT] (ACTNAME)

CREATE INDEX [IX_GMACT_PRJTBLKEY] ON [GMACT] (PRJTBLKEY)

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 10

Hey there.

Here's the table scripted using Management Studio:

CREATE TABLE [dbo].[ACCESS](

[TEMPFLAG] [bit] NOT NULL,

[varchar](50) NOT NULL,

[DESCRIPTN] [varchar](50) NOT NULL

) ON [PRIMARY]

Then the index also using Management Studio:

CREATE NONCLUSTERED INDEX ON [dbo].[ACCESS]

(

ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

And finally by running "exec sp_GetDDLa [ACCESS]":

CREATE TABLE [dbo].[ACCESS] (

[TEMPFLAG] BIT NOT NULL DEFAULT (0),

VARCHAR(50) NOT NULL,

[DESCRIPTN] VARCHAR(50) NOT NULL)

GO

CREATE INDEX ON [ACCESS] (CODE,) WITH FILLFACTOR = 90

Lowell SSC Guru Points: 323518 More actions · Answer 11

wierd, gotta be an issue i have not seen;

when i scripted your table and index, i get good, clean results.

can you try the latest sp_GetDDLa to be sure, this is the exact version i used:

sp_GetDDLa_Latest.txt (Returns Table)

sp_GetDDL_Latest.txt (Returns varchar(max) )

CREATE TABLE [dbo].[ACCESS] (

[TEMPFLAG] bit NOT NULL,

varchar(50) NOT NULL,

[DESCRIPTN] varchar(50) NOT NULL)

GO

CREATE INDEX ON [ACCESS] (CODE) WITH FILLFACTOR = 90

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 12

Hey again.

I saw in the SP that you insert the data into @Results, so I caught the contents of that - check out the index_name and index_columns_key columns(only 2 rows, since only one index):

index_name -

---

CODE

index_columns_key-

---

CODE,

And there's the magical comma - let me state in no unceratin terms that I'm no SQL ninja, I'm very new to this. It looks to me though that my index columns are being stored somewhere in sys.objects with the commas attached. Does this bring any ideas to mind?

Lowell SSC Guru Points: 323518 More actions · Answer 13

kbleeker (10/18/2011)
Hey again.
I saw in the SP that you insert the data into @Results, so I caught the contents of that - check out the index_name and index_columns_key columns(only 2 rows, since only one index):
index_name -
---
CODE
index_columns_key-
---
CODE,
And there's the magical comma - let me state in no unceratin terms that I'm no SQL ninja, I'm very new to this. It looks to me though that my index columns are being stored somewhere in sys.objects with the commas attached. Does this bring any ideas to mind?

yeah, that's part of the concatenation of all possible columns...it gets built with FOR XML PATH('') , but later in the query wrapped with this:

SELECT

LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,

which takes off the extra comma;

what i suspect is that specific table may have a column named --notice the space! that might be throwing the scripting off. i'm testing that scenarion now..i might need to quotename() each of the columns in the index to fix it...fast fix if that's the isssue.

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 SSC Guru Points: 323518 More actions · Answer 14

I tweaked both procs to quotename the index column names, and also to replace unix style code to end with vbCrLf;

same links as below, cna you retry by downloading the latest (again)

Lowell (10/18/2011)
wierd, gotta be an issue i have not seen;
when i scripted your table and index, i get good, clean results.
can you try the latest sp_GetDDLa to be sure, this is the exact version i used:
sp_GetDDLa_Latest.txt (Returns Table)
sp_GetDDL_Latest.txt (Returns varchar(max) )

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!

kbleeker SSC Veteran Points: 267 More actions · Answer 15

I'm sorry to say it, but the result is almost exactly the same:

CREATE TABLE [dbo].[ACCESS] (

[TEMPFLAG] bit NOT NULL DEFAULT (0),

varchar(50) NOT NULL,

[DESCRIPTN] varchar(50) NOT NULL)

GO

CREATE INDEX ON [ACCESS] (,) WITH FILLFACTOR = 90

I notice though that the comma is on the outside of the square brackets - does that not remove the column name from the possible problem?