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 ««123»»

How do I get the table definition ? Expand / Collapse
Author
Message
Posted Thursday, January 5, 2012 6:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1230705
Posted Thursday, January 5, 2012 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 27, 2014 9:53 AM
Points: 177, Visits: 250
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.



Post #1230763
Posted Monday, January 9, 2012 6:33 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1232372
Posted Monday, January 9, 2012 6:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 27, 2014 9:53 AM
Points: 177, Visits: 250
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 ?



Post #1232389
Posted Monday, January 9, 2012 7:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1232402
Posted Monday, January 9, 2012 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 27, 2014 9:53 AM
Points: 177, Visits: 250
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.

Post #1232416
Posted Monday, January 9, 2012 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:56 AM
Points: 2,013, Visits: 1,584
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

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T) | Open Network for Data Professionals...
LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1232424
Posted Monday, January 9, 2012 7:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:10 AM
Points: 1,886, Visits: 18,542
does this help?

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


__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________
Post #1232435
Posted Monday, January 9, 2012 8:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 27, 2014 9:53 AM
Points: 177, Visits: 250
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!




Post #1232448
Posted Tuesday, January 10, 2012 6:02 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, July 26, 2014 6:57 AM
Points: 1,502, Visits: 2,670
I would suggest that you search these forums for DMO or SMO which can generate some very nice scripts.
Post #1233103
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse