Get all Tables with Column Names and Data types from SQL Server Database

In this post ,we will generate the script that will return us all the table names with column names and data type of those columns. We often need this information. Let's say we are going to prepare mapping document for load the data from Source Database to Destination database. We can get the list of all tables with column names and data types from Source Database and Destination Database and then paste in Excel and map the required input columns to output columns for ETL Process.

SELECT T.Name                   AS TableName,
Schema_name(T.schema_id) AS SchemaName,
C.Name                   AS ColumnName,
Ty.Name                  AS ColumnDataType,
C.is_nullable            AS IsNullAble,
C.is_identity            AS IsIdentity 
FROM   sys.tables T
INNER JOIN sys.columns C
INNER JOIN sys.types Ty
ON C.system_type_id = Ty.system_type_id 
WHERE  T.is_ms_shipped = 0 

Fig 1: Get List of Tables with Column Names and Data Types from SQL Server Database


  1. Thanks a ton, this was exactly what I needed!

  2. The 1000 client base is your preparation datasets and with that current arrangement of information, you have to make, manufacture and train a model that can anticipate this specific viewpoint about your clients.Data Analytics Courses

  3. Know more about Data Analytics
    I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place

  4. New web site is looking good. Thanks for the great effort. Affordable CRM

  5. Just what I need! A course that would help me and my business know what is actually important in coming up with brand names. Would definitely recommend this! read more

  6. This is really a nice and informative, containing all information and also has a great impact on the new technology. Check it out here:Tables

  7. How would I edit this code to exclude the empty Tables (ie Tables that have columns, but no rows/records)?

  8. Though space is limited, one of the best parts about city life is having people over for a good time. transformer table

  9. I impressed by the quality of information on this website. There are a lot of good resources here. I am sure I will visit this place again soon.

    Company Logo Tablecloth