Export Table Schema only from SQL Server to Excel

Dom 941 Reputation points
2024-10-25T19:50:45.9333333+00:00

I need to export information about TABLES from SQL Server to Excel. I would need things like Table Name, Column Names, Data Types, etc. for each table I specify. Is there a way to do this from SSMS or are there tools out there that can do this? I know MS Access used to have a Database Documenter tool so I would like to find something like that for SS. Thanks.

SQL Server Other
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. LiHongMSFT-4306 31,566 Reputation points
    2024-10-28T02:26:19.2233333+00:00

    Hi @Dom

    Please run this query:

    SELECT * FROM INFORMATION_SCHEMA.columns WHERE table_name = 'YourTableName'
    

    Right-click on the results grid and save the file as a CSV file.

    Then open Excel and import the CSV file.

    Best regards,

    Cosmog


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2024-10-25T20:43:02.67+00:00

    Here is a query:

    SELECT s.name AS "Schema Name", t.name AS "Table Name", c.name AS "Column Name",
           ty.name AS "Data type", 
           CASE WHEN ty.name LIKE '%char' OR ty.name LIKE '%binary' THEN
                CASE WHEN c.max_length = -1 THEN 'MAX'
                     WHEN ty.name LIKE 'n%char' THEN convert(varchar(10), c.max_length / 2)
                     ELSE convert(varchar(10), c.max_length / 2)
                END
           END AS "Max length",
           CASE WHEN ty.name IN ('decimal', 'numeric') THEN c.precision END AS Precision,
           CASE WHEN ty.name IN ('decimal', 'numeric', 'time', 'datetime2', 'datetimeoffset') 
                THEN c.scale 
           END AS Scale
    FROM   sys.tables t
    JOIN   sys.schemas s ON s.schema_id = t.schema_id
    JOIN   sys.columns c ON t.object_id = c.object_id
    JOIN   sys.types ty ON ty.system_type_id = c.system_type_id 
    WHERE  ty.user_type_id <= 255
    ORDER  BY s.name, t.name, c.column_id
    
    
    

    To get the result into Excel, you can simply copy and paste.

    You can also go to the Data tab in the Ribbon and select Get Data and in this menu pick SQL Server database. Select Advanced to be able to use a query. Using this method may seem a little more cumbersome, but when you copy and paste, Excel may be too smart for its own good, and accidents can happen. When you run the query from Excel, Excel gets information about the data types, and does have to be smart.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.