List all the columns in the all the tables in all the databases

List all the columns in all the databases.


/*

CREATE TABLE #TmpColumns(
[TABLE_CATALOG] [nvarchar](128) NULL,
[TABLE_SCHEMA] [nvarchar](128) NULL,
[TABLE_NAME] [sysname] NOT NULL,
[COLUMN_NAME] [sysname] NULL,
[ORDINAL_POSITION] [int] NULL,
[COLUMN_DEFAULT] [nvarchar](4000) NULL,
[IS_NULLABLE] [varchar](3) NULL,
[DATA_TYPE] [nvarchar](128) NULL,
[CHARACTER_MAXIMUM_LENGTH] [int] NULL,
[CHARACTER_OCTET_LENGTH] [int] NULL,
[NUMERIC_PRECISION] [tinyint] NULL,
[NUMERIC_PRECISION_RADIX] [smallint] NULL,
[NUMERIC_SCALE] [int] NULL,
[DATETIME_PRECISION] [smallint] NULL,
[CHARACTER_SET_CATALOG] [sysname] NULL,
[CHARACTER_SET_SCHEMA] [sysname] NULL,
[CHARACTER_SET_NAME] [sysname] NULL,
[COLLATION_CATALOG] [sysname] NULL,
[COLLATION_SCHEMA] [sysname] NULL,
[COLLATION_NAME] [sysname] NULL,
[DOMAIN_CATALOG] [sysname] NULL,
[DOMAIN_SCHEMA] [sysname] NULL,
[DOMAIN_NAME] [sysname] NULL
) ON [PRIMARY]

insert into #TmpColumns
exec dbo.uspSearchAllColumnNames
@Database = null,
@Schema = null,
@Table = null,
@Column = '%office%'

select
*
from
#TmpColumns
*/

CREATE PROC [dbo].[uspSearchAllColumnNames](
@Database sysname = null,
@Schema sysname = null,
@Table sysname = null,
@Column sysname = null
)
as

DECLARE @Template NVARCHAR(2000)
DECLARE @Sql NVARCHAR(2000)
DECLARE @Catalog sysname
DECLARE @RowId int
DECLARE @MaxCnt int
SELECT
*
INTO #Columns
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
1=0
SET @Template = '
INSERT INTO #Columns
SELECT
*
FROM
[<>].INFORMATION_SCHEMA.COLUMNS
'
SELECT
IDENTITY(INT,1,1) AS RowID,
[Name]
INTO #DBNames
FROM
sys.databases
WHERE
owner_sid <> 0x01
SELECT
@RowId = 1,
@MaxCnt = COUNT(*)
FROM
#DBNames
WHILE (@RowId <= @MaxCnt)
BEGIN
SELECT
@Catalog = [Name]
FROM
#DBNames
WHERE
@RowId = RowId
SET @Sql = REPLACE(@Template, '<>', @Catalog)
EXEC sp_executesql @Sql
SET @RowId = @RowId +1
end


/*
--debugging
declare
@Database sysname,
@Schema sysname,
@Table sysname,
@Column sysname
SET @Column = '%vista%'
*/
SELECT
*
FROM
#Columns
where
(@Database IS NULL OR TABLE_CATALOG LIKE @Database)
AND (@Schema IS NULL OR TABLE_SCHEMA LIKE @Schema)
AND (@Table IS NULL OR TABLE_NAME LIKE @Table)
AND (@Column IS NULL OR COLUMN_NAME LIKE @Column)
ORDER BY
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME


DROP TABLE #Columns
DROP TABLE #DBNames