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

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

  • 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]

  • 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.

  • 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/

  • 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]

  • 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.

  • 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/

  • 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.

  • 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.

  • 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?

  • 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]

  • To display structure sp_help 'table_name'

    Regards
    Durai Nagarajan

  • 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)

  • +1 to jeff

    Regards
    Durai Nagarajan

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

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

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