SQL Server Made Easy – Search for data elements or columns

Ever need to find all the places where a data element might appear in a database?  You might need to make a change to the format of the column, or you just need to know where it is used in order to make a global update.  This can be very time consuming if you try to do it the hard way.  Here’s a few easy to use scripts for you to run instead, using Macola Progression SQL and Macola ES (ERP systems) databases as  examples:

I. Macola Progression SQL database:

a. Example: Find all tables in the data base that contain the data element “Prod_Cat”

select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
from dbname.information_schema.columns
where column_name = ‘prod_cat’
order by table_name

b. Example: Find all tables in the data base that contain a data element with the value of “Prod” somewhere in the element name.

select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
from dbname.information_schema.columns
where column_name like ‘%prod %’
order by table_name

II. Macola ES Data base

a. Example: Find columns with a specific name

select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where column_name = ‘prod_cat’
order by table_name

b. Example: Find all tables in the data base that contain a data element with the value of “Prod” somewhere in the element name.

select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE,COLUMN_DEFAULT
from information_schema.columns
where column_name like ‘%prod%’
order by table_name

c. Example: Find columns from a specific table

select  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where table_name = ‘cicmpy’
order by table_name