Get list of all the table names, column names, and column data from SQL Server Database

Get list of all the table names, column names, and column data from SQL Server Database

In this post, we will create the script that will provide a list of all the table names, column names, and column data types. We frequently need this Information. Let's imagine that we are planning to create a mapping document to load data from the source database to the target database. We can copy and paste the list of all tables from the source and destination databases into Excel to paste the necessary input values to output columns for the 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