Tag: find datatype

There are many reasons why you would want to find a specific column or data type in your database. Read on to find some ways to accomplish this.

Read more: Find Columns In Your Database

Here is one way. It is slow and involves opening every table in SSMS.

Then you need to expand the table and then the columns to display each field.

One quick way is to use the SP_COLUMNS procedure. The easiest way to run it is to provide the table name that you wish to look for.

EXEC sp_columns @table_name = 'tbl_products'

The quickest way in my opinion is to use the INFORMATION_SCHEMA.COLUMNS view. In the query below, I am searching for the table name: tbl_products. This lists all of the columns and their data type.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tbl_products'

Also with this view, you can search for only columns of a certain data type. In the example below, I want to see every table and column that has a data type of TEXT. With this query it is very simple.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='text'

To read more about these Microsoft provided views/stored procedures, please follow the links below.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-columns-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/system-information-schema-views-transact-sql?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver16