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
               ON T.OBJECT_ID = C.OBJECT_ID
       INNER JOIN sys.types Ty
               ON C.system_type_id = Ty.system_type_id 
WHERE  T.is_ms_shipped = 0 
ORDER  BY T.name 

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