how to script out create table script of all tables in a database

Question

Post reply

how to script out create table script of all tables in a database

Dhruva_51

SSChampion

Points: 10521
More actions
August 6, 2013 at 7:52 am

#276724

I would like to script out all create table script in a database and save it in a table. How to perform this operation?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Why do you want to do this? All the information in the script is already available in system tables and views.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Dhruva_51 SSChampion Points: 10521 More actions · Answer 2

I am working on a project to help developers to know the relationship between the tables. Let us assume pk in table A is foreign key constrain in table B,C and D. So I want to display all the table info in a window. Kind of ERD but as we have too many tables in our prod environment its' not possible to display all the table info in a single window.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

muthyala_51 (8/6/2013)
I am working on a project to help developers to know the relationship between the tables. Let us assume pk in table A is foreign key constrain in table B,C and D. So I want to display all the table info in a window. Kind of ERD but as we have too many tables in our prod environment its' not possible to display all the table info in a single window.

Why not use a database diagram? That is pretty much what they are intended to be used for.

_______________________________________________________________

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/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

You should be able to get all you need from these views:

INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.COLUMNS

sys.schemas

sys.tables

sys.columns

There may be other system tables or views that could be helpful. You just need to take the time to learn what's available in system views.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Dhruva_51 SSChampion Points: 10521 More actions · Answer 5

Why not use a database diagram? That is pretty much what they are intended to be used for.

The problem is we have too many tables which are inter related. If we go with database diagram it will be a huge figure with too much information. I am just trying to stream line the process which will be easy for new developers to understand the database structure.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

muthyala_51 (8/6/2013)
Why not use a database diagram? That is pretty much what they are intended to be used for.
The problem is we have too many tables which are inter related. If we go with database diagram it will be a huge figure with too much information. I am just trying to stream line the process which will be easy for new developers to understand the database structure.

Understood. The main advantage of the diagram is that you don't have to include every single table. I have often used diagrams as a learning tool for new employees. Just cut down the number of tables and explain that there are some additional tables but the diagram is only showing the major ones.

_______________________________________________________________

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/

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

In answer to your original question: Display the tables in the "Object Explorer" window. Then select the tables you want (all or some), right click, "Script Table as Create", then choose the option you want.

Go to "Tools -> Options -> Scripting" to modify scripting rules.

Dhruva_51 SSChampion Points: 10521 More actions · Answer 8

I am planning to work on a dashboard kind of tool , such like when we click on a table it should display all the tables dependent on it and if we like to see the structure, it should be able to display the structure of all the tables.

Dhruva_51 SSChampion Points: 10521 More actions · Answer 9

do we have any already built in tools which will help me to achieve this? I tried to use Dezign which works better to some extent. Any suggestions?

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Have you looked at what Visio can do? I've used it many times for database diagrams.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

durai nagarajan SSCoach Points: 15778 More actions · Answer 11

To display structure sp_help 'table_name'

Regards
Durai Nagarajan

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 12

To generate all of the tables, indexes, FK's, etc, just right click on the database in the Explorer window, click on "Tasks", and then on "Generate Scripts".

This (generating scripts for everything in the database) is , in my humble opinion, one of the worst ways to teach developers about relationships and DRI. It's much easier and effective to make a simple example using two or three such tables along with the "Diagram" functionality in SQL Server. That way, they don't actually need to know much about the tables and can concentrate on learning about DRI, instead.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durai nagarajan SSCoach Points: 15778 More actions · Answer 13

durai nagarajan

SSCoach

Points: 15778

August 8, 2013 at 12:25 am

#1639627

+1 to jeff

Regards
Durai Nagarajan

Chris Cain Valued Member Points: 64 More actions · Answer 14

You can use the built in script generator in SSMS... right click on the db and select 'Generate Scripts'