2018-10-23

SQL View to search in all columns of all tables

Often, I need to search a column name in a database… but I’ve not the table name. If you are like me, this SQL View can help you.

IF EXISTS(SELECT * FROM sys.views WHERE name = 'AllColumns')
DROP VIEW AllColumns

GO

CREATE VIEW AllColumns
AS
SELECT sys.tables.name AS "TableName",
sys.columns.name AS "ColumnName",
sys.systypes.name AS "ColumnType",
sys.columns.max_length AS "ColumnSize",
sys.columns.is_nullable AS "ColumnNullable",
sys.columns.scale AS "ColumnScale",
sys.columns.collation_name AS "ColumnCollation"
FROM sys.tables
INNER JOIN sys.columns ON sys.tables.object_id = sys.columns.object_id
INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id

Leave a Reply

Your email address will not be published. Required fields are marked *