February 12, 2016 at 5:49 am
In SSMS you can get the def of a table by:
Right Click on table, Script Table as, CREATE to, New Query Editor Window
Question1:
Is there any way I could do the same via a SQL query.
( if you could just show me one table that will be fine )
Question2: It would be ideal if you could also show me to get the def of an index.
( I have taken care of the stored procs and functions via ssis, so no need to worry on those objects ).
February 12, 2016 at 5:56 am
mw112009 (2/12/2016)
In SSMS you can get the def of a table by:Right Click on table, Script Table as, CREATE to, New Query Editor Window
Question1:
Is there any way I could do the same via a SQL query.
( if you could just show me one table that will be fine )
In the interests of letting you experiment a little, I will say the answer is yes and you should look at the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS system views. You can use those two items to script out your definitions.
Try it out. If you're having problems, post what code you've tried and what errors / problems you're having, and we'll nudge you in the right direction.
As far as the indexes go, I don't know the system views off the top of my head. I'll check around.
February 12, 2016 at 6:28 am
I am not trying to be mean here....
But the real answer is...
"[There is no way to do that in one or several SQl statememts]"
However I found the perfect resource out in the internet. One person was kind enough to share a very long stored proc he had written that works perfectly ( I am hoping it is accurate, I never went to the extent of checking it ). I just ran it once and it looks perfect.
February 12, 2016 at 6:58 am
mw112009 (2/12/2016)
I am not trying to be mean here....But the real answer is...
"[There is no way to do that in one or several SQl statememts]"
Well, I'm sorry to disagree with you as I have done it on several occasions. But if you want to believe you can't do something that you wanted to be able to do, then please feel free.
February 12, 2016 at 7:24 am
mw112009 (2/12/2016)
I am not trying to be mean here....But the real answer is...
"[There is no way to do that in one or several SQl statememts]"
However I found the perfect resource out in the internet. One person was kind enough to share a very long stored proc he had written that works perfectly ( I am hoping it is accurate, I never went to the extent of checking it ). I just ran it once and it looks perfect.
May I suggest that you always thoroughly check code found online in future and make sure you 100% understand it BEFORE running it. If something goes wrong because you don't understand what it does, you're the only one to blame.
Also, Brandie was kind enough to share the relevant information with you, a 'thankyou but I've now resolved this' probably wouldn't go a miss.
February 12, 2016 at 7:24 am
Well then can you be kind enough to share that code with us please ?
If you are not willing that is fine. No hard feelings!
Have a great day!
BTW - Once you share your code I will direct you to the web site where I found the code. Guranteed! Or If I don't hear from anyone I will yet post it by tomorrow. 🙂
February 12, 2016 at 7:49 am
mw112009 (2/12/2016)
Well then can you be kind enough to share that code with us please ?
The reason I haven't shared the code is because this is a great time to learn how to use the system tables. The key is the CONVERT or CAST keywords and concatenation to dynamically create everything you need.
Look at the system views I gave you. The information is all there. All you have to do is play with it.
February 12, 2016 at 7:54 am
mw112009 (2/12/2016)
Well then can you be kind enough to share that code with us please ?If you are not willing that is fine. No hard feelings!
Have a great day!
BTW - Once you share your code I will direct you to the web site where I found the code. Guranteed! Or If I don't hear from anyone I will yet post it by tomorrow. 🙂
You are asking others to share what they have done but your are withholding information at the same time. Why? You even said you don't know if it is accurate. How about you post it and we can take a look at it.
BTW, Brandie is spot on. This kind of information is readily available and has been since at least as far back as sql server 7. I have written some utilities that make full use of this data. I wrote mine before we had the INFORMATION_SCHEMA views. I just directly queried sys.objects, sys.columns, and sys.systypes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 12, 2016 at 8:11 am
I went deep, deep down this rabbit hole, and built a procedure that i can call from tsql .
tin the long run, you have to use the sys. schemas and not the information_schemas stuff, because there are a lot of SQL-specific details that are not exposed;
i do this all the time; I've got two versions, one that returns rows, and one that returns a varchar(max)
EXECUTE sp_GetDDLa Tablename;
EXECUTE sp_GetDDL Tablename;
it's not perfect, since there's a lot of things that are new that it doesn't handle yet, which is why the discussion on it is eight pages deep; other contributers offer modifications that i incorporate in big batches every once in a while.
the latest public iteration is here:
http://www.sqlservercentral.com/Forums/FindPost1672853.aspx
Lowell
February 12, 2016 at 8:22 am
Sean:
As promised I will share it later. Why ? I picked the sp from a website ( just a hour ago ). Before I share it, let me make sure it does the job properly.
February 12, 2016 at 8:29 am
Lowell:
The one I picked ( I did a quick search ) uses the following objects.
You mentioned stuff from INFORMATION_SCHEMA is lacking some info.
Can you mention a few ?
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.COLUMNS
sys.identity_columns
sys.columns
information_schema.table_constraints
sys.indexes
February 12, 2016 at 8:32 am
Sean:
Here you go:
Whether it is accurate or not.. Your decision
http://sqlindia.com/generate-table-definitions-using-tsql-sql-server/
February 12, 2016 at 8:37 am
mw112009 (2/12/2016)
Lowell:The one I picked ( I did a quick search ) uses the following objects.
You mentioned stuff from INFORMATION_SCHEMA is lacking some info.
Can you mention a few ?
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
INFORMATION_SCHEMA.COLUMNS
sys.identity_columns
sys.columns
information_schema.table_constraints
sys.indexes
it's in the example you posted right there.
sys.columns, sys.identity/index/esindex_cols columns are not in information_schemas; to get the identity column, you have to use either a objectproperty function or the sys data.
if you have to go to the sys.* views , since they are natively linked by object_id, you should use an all or nothing approach, in my opinion.
mine is overlooking small stuff right now, like SPARSE columns, foreign key cascade stuff, compression on indexes, some other stuff.
All that easy stuff to add, and i've been prototyping it, but i'm running at 110% at work right now, no time for fun, altely.
Lowell
February 12, 2016 at 8:41 am
Check out this article [/url]which generates audit tables based on your tables using xsl. If you're already using ssis for the other scripts, you could leverage this to do your transformation. (It's an old article - so doesn't cover some of the latest data types, but could be relatively easily adapted.)
David McKinney.
February 12, 2016 at 8:43 am
Here is a great article discussing the shortcoming of the INFORMATION_SCHEMA views. http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply