Command for retrieve structure script of a table

Question

Command for retrieve structure script of a table

shamshudheen

SSCrazy

Points: 2390
More actions
April 11, 2008 at 6:31 am

#205692

Hi guys
Like Generate script in enterprisemanager is there a command to retrieve the structure script for a given table.
doing sp_help command giving categorized information. in fact i want same structure when the table was created
appreciating your help
shamsudheen

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Do you mean you want a script to generate the table? I ask because sp_help does show you the information about the table that defines it.

You could write up your own query against the INFORMATION_SCHEMA views.

To generate scripts you can use the SSMS gui, or do some SMO programming.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shamshudheen SSCrazy Points: 2390 More actions · Answer 2

Grant Fritchey (4/11/2008)
Do you mean you want a script to generate the table? I ask because sp_help does show you the information about the table that defines it.
You could write up your own query against the INFORMATION_SCHEMA views.
To generate scripts you can use the SSMS gui, or do some SMO programming.

Grant,

Thanks for your response

what is INFORMATION_SCHEMA view, where i can find it and what is SMO programming

Thanks in advance

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

The INFORMATION_SCHEMA views are built in to every database in the system. It's just a cleaner way of accessing the meta-data, the information that describes your database and its structures, from TSQL. They're in SQL 2000,2005,2008. To see them in Management Studio, just expand your database, then views, then system views. You'll see them all right there.

SMO (SQL Management Objects) is a way of programming against SQL Server using a .NET language (like C# or VB). It has great scripting capabilities and because you're using a programming language, it enables you to exercise a lot of control over how you do it.

I'm still not sure I'm providing you the right information to answer your question.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shamshudheen SSCrazy Points: 2390 More actions · Answer 4

ya Grant you are providing right solution

i am using sql svr 2000 , i dont find INFORMATION_SCHEMA views

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

OK.

They're in there. I'm so used to 2005 (you are posting in a 2005 forum, by the way) that I thought you'd see them. You won't. Here's the answer why. However, you can still use them. Just follow the instructions on the link.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

shamshudheen SSCrazy Points: 2390 More actions · Answer 6

Grant Fritchey (4/11/2008)
Do you mean you want a script to generate the table? I ask because sp_help does show you the information about the table that defines it.
You could write up your own query against the INFORMATION_SCHEMA views.
To generate scripts you can use the SSMS gui, or do some SMO programming.

ya grant thanks , i found information about INFORMATION_SCHEMA views . as you told i have to write a query using these views

thanks once again