2019-03-22

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 *