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. Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time sql dba training

  4. Very nice job... Thanks for sharing this amazing and educative blog post! ExcelR Data Analytics Courses

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

  6. Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

  7. This is my first time visit here. From the tons of comments on your articles.I guess I am not only one having all the enjoyment right here! ExcelR Data Analytics Courses In Pune