Search SQL Database for Instances of Column Data Type

I needed to search an entire database to find 64-bit integer data types were being used to make sure I was handling them correctly. I needed to create a query to find all the columns in the database that are SQL bigint type.

I found a and modified it to get the location information I needed.

SELECT c.[TABLE_CATALOG], c.[TABLE_SCHEMA], c.[TABLE_NAME], c.[COLUMN_NAME], c.[DATA_TYPE], t.[TABLE_TYPE]
FROM INFORMATION_SCHEMA.columns c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON t.[TABLE_NAME] = c.[TABLE_NAME]
WHERE c.[DATA_TYPE] = 'bigint' AND t.[TABLE_TYPE] = 'BASE TABLE'
ORDER BY [TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME]

Other Uses

  • Search for any SQL type by replacing the 'bigint' type specified with a different SQL type on line 5 (i.e. ‘int‘ or ‘varchar‘).
  • See all available column information by replacing the SELECT list with “SELECT c.*, t.TABLE_TYPE” on line 1.
  • Search views instead of tables by replacing ‘BASE TABLE‘ with ‘VIEW‘ on line 5.
  • Search for both tables and views by removing line the entire “AND t.[TABLE_TYPE] = 'BASE TABLE'” clause on line 5.

Related Posts