Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I get the table definition ?


How do I get the table definition ?

Author
Message
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
george sibbald (1/4/2012)
am I missing something here? whats wrong with sp_help?

you can look at the code in sp_help to see how it's done if you only want a subset of what sp_help gives you.


Nothing at all wrong with sp_help. Just depends on what you're looking for in terms of table definition results.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
mw112009
mw112009
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 845
sp_help 'Table Name' throws out many layers of information. This would be ideal for just checking one table at a time just to get information.

What we need is a way to get the "CREATE TABLE ABC ( mo VARCHAR(10) )" definition in text form.
This way, via SSIS, I can save all definitions to a text file.
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Is it absolutely necessary to have SSIS output them for you? If not, you can use the object-scripting wizard in SSMS to generate table scripts any time you need to.

Right-click the database in the object browser, Task->Script Database, select the options you want and output to a text file. Usually a pretty fast and easy task.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
mw112009
mw112009
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 845
Agreed!
However, this gives us all the tables. Not a problem.


My next question is, can this task be automated via a SSIS ?
What I mean is can I have a job that runs everyday at 6 PM and have the definitions file being written to
some directory ?
Dev
Dev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 1598
My next question is, can this task be automated via a SSIS ?
What I mean is can I have a job that runs everyday at 6 PM and have the definitions file being written to
some directory ?


It's possible. How? You need to spend some time understanding SSIS data flows, if you are less familiar with it.

Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad

mw112009
mw112009
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 845
I am definitely familiar with SSIS.
Give me a clue here.

I guess I am not explaining myself clearly. What I need is the same script that can be generated
via a a menu item ( Right Click on Database and then select "TASKS" and "GENERATE SCRIPTS" )
be able to be produced via a SSIS script.
Dev
Dev
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1944 Visits: 1598
I read the completer thread (now). I believe you are not able to generate the resultset (data dictionary) as the first step. If your query gives the output you are looking for, you may redirect it to a file.

Dev

*** Open Network for Database Professionals ***
http://www.linkedin.com/in/devendrashirbad

J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33029
does this help?

http://www.sqlservercentral.com/scripts/T-SQL/67149/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

mw112009
mw112009
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 845
Yes Sir!
This will do

I may have to modify the script to take care of some quirks but for now, This is the best!
The script only covers tables in the dbo schema. Some of our tables sit inside other schemas.

Anyway, this will help. Thanks!
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 3018
I would suggest that you search these forums for DMO or SMO which can generate some very nice scripts.
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