none
Exporting SQL Server schema to Excel

    Question

  •  

    Hi Everyone.. I am a complete novice to SQL Server 2005. I have the express version installed and have a database on it.

     

    I have to start new work and need to see what locations for data I already have before designing new columns. I would like to see a single excel worksheet with all of the tables with all of the columns and their datatype and length.

     

    Is this possible?

     

    Séan

    Monday, March 17, 2008 3:42 PM

Answers

  • You can dump all the column definitions using the following query:

     

    Code Snippet

    select * from INFORMATION_SCHEMA.COLUMNS

     

     

    Run this from the database you want information on and send the results to a file rather than to the Resuts window. You should be able to open the result file in Excel and put the data into any format you want. If you need additional information beyond just the columns, i.e. Keys, Indexes, etc. you'll need something a bit more advanced:

    Code Snippet

    sp_MSForEachTable 'sp_help ''?'''

     

     

    The sp_MSForEachTable procedure is not documented in BOL, but you can find it documented in other locations, for example http://www.databasejournal.com/features/mssql/article.php/3441031. (Note: If it's not documented in BOL, we don't promise it won't change so be careful about using this in programing that is going to live for a long time. For single use like this you should be fine.)

     

    This code will run the sp_help procedure, which returns much more information about each table, for each table in the database. The sp_help function will return multiple tables of information, so it will be a bit harder to organize it in Excel than just using the column list from INFORMATION_SCHEMA, but it will still be faster than trying to type it all in manually.

     

    Regards,

    Mike

    Monday, March 17, 2008 4:32 PM

All replies

  • Hi Sean,

     

    You should really be using a tool such as Management Studio Express to look at the design of your database. You can download it from http://msdn.microsoft.com/vstudio/sql/express/download.

     

    Regards,

    Mike

    Monday, March 17, 2008 3:58 PM
  • Hi Mike,

     

    I do have this tool. I need to write a integration document to show the field to field migration for this new system. When I know what I need, anything I have that I don't need will be dropped, anything new will need to be added - but this needs to be documented.

     

    Thank you for your help... and in anticipation of more help Smile

     

    Séan

    Monday, March 17, 2008 4:04 PM
  • You can dump all the column definitions using the following query:

     

    Code Snippet

    select * from INFORMATION_SCHEMA.COLUMNS

     

     

    Run this from the database you want information on and send the results to a file rather than to the Resuts window. You should be able to open the result file in Excel and put the data into any format you want. If you need additional information beyond just the columns, i.e. Keys, Indexes, etc. you'll need something a bit more advanced:

    Code Snippet

    sp_MSForEachTable 'sp_help ''?'''

     

     

    The sp_MSForEachTable procedure is not documented in BOL, but you can find it documented in other locations, for example http://www.databasejournal.com/features/mssql/article.php/3441031. (Note: If it's not documented in BOL, we don't promise it won't change so be careful about using this in programing that is going to live for a long time. For single use like this you should be fine.)

     

    This code will run the sp_help procedure, which returns much more information about each table, for each table in the database. The sp_help function will return multiple tables of information, so it will be a bit harder to organize it in Excel than just using the column list from INFORMATION_SCHEMA, but it will still be faster than trying to type it all in manually.

     

    Regards,

    Mike

    Monday, March 17, 2008 4:32 PM
  • Thanks Mike,

    I got my solution..

    Manas Sahu Visit my Blogs: http://expertdevelopersblog.blogspot.com http://cloudcomputinghub.wordpress.com
    • Edited by Manas Sahu Friday, February 26, 2010 10:51 AM spelling mistake
    Friday, February 26, 2010 10:51 AM
  • Check this : http://nissankg.wordpress.com/2010/03/03/sql-database-schema-to-excel/
    Thursday, March 04, 2010 4:55 AM
  • Top man with top tip - Thankyou very much

    ****************

    You can dump all the column definitions using the following query:

     

    Code Snippet

    select * from INFORMATION_SCHEMA.COLUMNS

     

     

    Run this from the database you want information on and send the results to a file rather than to the Resuts window. You should be able to open the result file in Excel and put the data into any format you want. If you need additional information beyond just the columns, i.e. Keys, Indexes, etc. you'll need something a bit more advanced:

     

    Code Snippet

    sp_MSForEachTable 'sp_help ''?'''

     

     

    The sp_MSForEachTable procedure is not documented in BOL, but you can find it documented in other locations, for example http://www.databasejournal.com/features/mssql/article.php/3441031. (Note: If it's not documented in BOL, we don't promise it won't change so be careful about using this in programing that is going to live for a long time. For single use like this you should be fine.)

     

    This code will run the sp_help procedure, which returns much more information about each table, for each table in the database. The sp_help function will return multiple tables of information, so it will be a bit harder to organize it in Excel than just using the column list from INFORMATION_SCHEMA, but it will still be faster than trying to type it all in manually.

     

    Regards,

    Mike


    • Proposed as answer by Rasooli Friday, May 06, 2011 9:10 AM
    Friday, May 06, 2011 9:09 AM