List all columns with table names from database based on data type of column

Hello All,

Sometimes we required to find a list of columns with table names based on data type of coulmn from sql server database, it is tedious task to search from hundreds of tables. You can find it easily by using below query.

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'DATATYPE OF COLUMN' --e.g. 'tinyint', 'ntext' etc.
AND OBJECT_NAME(c.OBJECT_ID) IN
(
    select TABLE_NAME from information_schema.tables
)
ORDER BY c.OBJECT_ID;

List all Default Values in a SQL Server Database

Hello Everyone,

See the below query to find the default value of all columns in all tables of a database.

SELECT obj.name as 'Table', col.name as 'Column',
object_definition(default_object_id) AS [DefaultValue]
FROM   sys.objects obj INNER JOIN sys.columns col
ON obj.object_id = col.object_id
where obj.type = 'U' AND object_definition(default_object_id) IS NOT NULL