Information Schema Views were introduced in Microsoft SQL Server 2000. They provide a method of querying database meta-data without directly accessing system tables. Information Schema Views are less cryptic than system tables (no type code or bit map translations), and require fewer table joins within a query, depending on the data you are interested in.

Of all the Information Schema views, I find myself using the “INFORMATION_SCHEMA.columns” the most. The INFORMATION_SCHEMA.COLUMNS view returns one row from each column within a table.

Of the columns exposed by this view, the ones I find most useful are:

TABLE_NAME

COLUMN_NAME

ORDINAL_POSITION

COLUMN_DEFAULT

IS_NULLABLE

DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH

NUMERIC_PRECISION

NUMERIC_SCALE

COLLATION_NAME

As with most of my articles, I’ll provide you with some Transact-SQL examples that I find useful in my day-to-day work. You can integrate them into your own code as you see fit, or find variations on the themes discussed within this article. This article assumes you are using SQL Server 2000 and have db_owner or sysadmin permissions. If you do not have these permissions, the INFORMATION_SCHEMA.columns view will only show those columns accessible to your current user login session.

Scenario #1: Alphabetizing columns

A simple one… You have a table you wish to work with that has hundreds of columns. The columns are in no particular order, and you would like to display them in alphabetical order.  

USE Northwind

SELECT COLUMN_NAME,

DATA_TYPE,

CHARACTER_MAXIMUM_LENGTH,

NUMERIC_PRECISION,

NUMERIC_SCALE,

IS_NULLABLE,

COLUMN_DEFAULT,

ORDINAL_POSITION

FROM INFORMATION_SCHEMA.columns

WHERE TABLE_NAME = 'Orders'

ORDER BY COLUMN_NAME

If you want to view the columns by actual column position, you can use “ORDER BY ORDINAL_POSITION” instead. 

Scenario #2: Find those schema changes

You have two tables that used to have the same schema, but now have had modifications made to them. You need to find out which columns have been deleted, added, or modified. To illustrate this example, we will use two example tables, “TableA” and “TableB”: 

CREATE TABLE EmployeeA ( EmployeeID int IDENTITY (1,1),
FirstName char(20),
LastName char(20),
HireDate datetime)
 
CREATE TABLE EmployeeB ( EmployeeID int IDENTITY (1,1),
FirstName char(20),
LastName char(20),
HireDate datetime)

Now let’s make a few changes to EmployeeB. We will modify a column data type, add a new column, and drop an existing column.

ALTER TABLE EmployeeB
ALTER COLUMN LastName varchar(10)
ALTER TABLE EmployeeB
ADD TerminationDate datetime 
ALTER TABLE EmployeeB
DROP COLUMN HireDate

Now to find those new fields added to EmployeeB:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'EmployeeB' AND
COLUMN_NAME NOT IN
(SELECT COLUMN_NAME
 FROM INFORMATION_SCHEMA.columns
 WHERE TABLE_NAME = 'EmployeeA')

This query identified those columns with modified data types:

SELECT A.COLUMN_NAME, 
A.DATA_TYPE as 'Original Data Type',
A.CHARACTER_MAXIMUM_LENGTH as 'Original Max Length',
A.NUMERIC_PRECISION as 'Original precision',
A.NUMERIC_SCALE as 'Original scale',
B.DATA_TYPE as 'New Data Type',
B.CHARACTER_MAXIMUM_LENGTH as 'New Max Length',
B.NUMERIC_PRECISION as 'New precision',
B.NUMERIC_SCALE as 'New scale'
FROM INFORMATION_SCHEMA.columns A,
INFORMATION_SCHEMA.columns B
WHERE A.TABLE_NAME = 'EmployeeA' AND
B.TABLE_NAME = 'EmployeeB' AND
A.COLUMN_NAME = B.COLUMN_NAME AND
((A.DATA_TYPE <> B.DATA_TYPE) OR
(A.CHARACTER_MAXIMUM_LENGTH <> B.CHARACTER_MAXIMUM_LENGTH OR
 A.NUMERIC_PRECISION <> B.NUMERIC_PRECISION OR
 A.NUMERIC_SCALE <> B.NUMERIC_SCALE)) 

This query finds those columns dropped from EmployeeB: 

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'EmployeeA' AND
COLUMN_NAME NOT IN
(SELECT COLUMN_NAME
 FROM INFORMATION_SCHEMA.columns
 WHERE TABLE_NAME = 'EmployeeB')

Scenario #3: Compare collation

Ever imported a database from a SQL Server Instance that uses a different default collation from the destination SQL Server Instance?  You can run into issues with your queries if you attempt to join columns with different collations. If it means not losing data, you can often modify the collation to the destination database collation for each varying column. Use INFORMATION_SCHEMA.columns to identify which columns have the differing collation.  

SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
IS_NULLABLE,
COLUMN_DEFAULT,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
WHERE COLLATION_NAME = 'SQL_Latin1_General_CP1_CI_AS'
ORDER BY COLUMN_NAME

Scenario #4: Find that column, make it bigger

You have a column that is used in multiple tables in your database. You need it expanded based on your new requirements, wherever it may exist. This example uses a query that makes a query. The output will generate the ALTER statement required to expand the column.  (Keep in mind that if the column is used for an FK reference, or is indexed, you may have to drop those references first). This example expands the maximum column length for the “LastName” column to 30, for any table in the database.  

SELECT 'ALTER TABLE ' +
  Table_name +
 ' ALTER COLUMN ' +
  Column_name +
 ' ' + 
  data_type +
 '(30)' 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'LastName' 

You can then execute the output of this query to modify the column data type size. Use caution to ensure that you are only modifying the intended columns. This script will need modification if you allow spaces in column names (not generally good practice).

Hope these examples will come in handy in your day-to-day development and administration.