Get DDL for any SQL 2005 table

Question

Post reply

Get DDL for any SQL 2005 table

Viewing 8 posts - 121 through 127 (of 127 total)

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

jhzhao88 SSC Journeyman Points: 81 More actions · Answer 1

Thanks! I made the change as well. Hopefully this can be added to the latest version.

jhzhao88 SSC Journeyman Points: 81 More actions · Answer 2

This change needs to be in the latest version. I see the issue as well.

Lowell SSC Guru Points: 323518 More actions · Answer 3

spcghst440 / Tim C, thank you very much for pointing out that gap!

I've been using this for literally a decade or more, and so far , in my environment, i had not tripped over a object referencing a table not in it's same schema. great catch.

I have updated the links above to have the latest changes spcghst440 pointed out.

the things that are still technically a gap that i know of are:

if a table is partitioned, i think it does not return the table definition, but that may be related to item 2 below.
if the database has any custom data types with really long names, it breaks the scripting/formatting logic that is looking for the largest data type, and ends up nulling out the column list. i was grabbing the length to make the formatting of columns/datatypes/null align and look pretty, and something in there breaks my logic.

i use this proc as a keyboard shortcut literally hundreds of times per week. it's helped me for years. I hope it helps you too.

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!

Lowell SSC Guru Points: 323518 More actions · Answer 4

oh yeah, and stubborn old me only likes varchars..so if you have a table that has a name with nvarchar, like below, you'd have to find and replace all the varchar statements to nvarchars to get it to work:

CREATE TABLE [私のテーブル]
([Id] INT ,
[列名] NVARCHAR(30) )

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!

jhzhao88 SSC Journeyman Points: 81 More actions · Answer 5

Thanks for the quick fix Lowell! I too use this proc as a keyboard shortcut for years on daily basis. It has been very helpful. We do have RedGate to get the definitions for most of the tables but the system versioning table scripting are not added to RedGate yet. So I will keep using this one as it is very convenient.

Xiao

jhzhao88 SSC Journeyman Points: 81 More actions · Answer 6

Azure managed instance ... Could not mark system object... Could not find stored procedure 'sys.sp_MS_marksystemobject'. Is there a work around?

https://learn.microsoft.com/en-us/answers/questions/146642/azure-managed-instance-could-not-find-stored-proce

Thanks,

Xiao

This reply was modified 6 months, 1 week ago by jhzhao88.
This reply was modified 6 months, 1 week ago by jhzhao88.

Lowell SSC Guru Points: 323518 More actions · Answer 7

In Azure, the procedures sp_GetDDL and sp_GetDDLa would have to be installed in each database; so 10 dbs = 10 copies, sorry

we don't have the ability to leverage ability to mark a procedure as a system object at all in Azure SQL databases.

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!

jhzhao88 SSC Journeyman Points: 81 More actions · Answer 8

jhzhao88

SSC Journeyman

Points: 81

June 19, 2024 at 9:16 pm

#4429843

Thanks Lowell ! Appreciate the quick response.