As a SQL Server developer, it is essential to have a handy script that allows you to quickly list all the columns in a database along with their respective data types. Five years ago, I wrote a script for this purpose, and upon revisiting it recently, I found it to be highly relevant and useful. In fact, I have made some enhancements to the script to provide even more valuable information.

Before we dive into the updated script, let me emphasize the importance of visualizing the final script when initially writing it. As our needs evolve over time, so do our scripts. I personally use every script that I share on this blog, ensuring that it meets my requirements at the time of writing.

Without further ado, here is the updated script:

SELECT s.name AS 'schema',
       ts.name AS TableName,
       c.name AS column_name,
       c.column_id,
       SCHEMA_NAME(t.schema_id) AS DatatypeSchema,
       t.name AS Datatypename,
       t.is_user_defined,
       t.is_assembly_type,
       c.is_nullable,
       c.max_length,
       c.PRECISION,
       c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id
ORDER BY s.name, ts.name, c.column_id

This script not only lists all the columns in the database but also provides additional information such as the schema, table name, column ID, data type schema, data type name, whether the data type is user-defined or assembly type, nullability, maximum length, precision, and scale.

I would love to hear your thoughts on this script. If you believe there is something missing or have any suggestions for improvement, please let me know in the comments. Together, we can make this page a valuable bookmark for all SQL Server developers.