Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Problem Solving With Information Schema Columns

By Joseph Sack,

Problem Solving with INFORMATION_SCHEMA

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.
Total article views: 13464 | Views in the last 30 days: 8
 
Related Articles
FORUM

Retrieve column headers for INFORMATION_SCHEMA

Get INFORMATION_SCHEMA column headers

FORUM

Obtaining information about table using System views

how to get column length

FORUM

Get longest length of data in a column

Get longest length of data in a column

FORUM

INFORMATION_SCHEMA.COLUMNS <> Table Definition

I have a table that has one particular column of VARCHAR datatype. The entry in INFORMATION_SCHEMA.C...

FORUM

INFORMATION_SCHEMA.COLUMNS reference

How do you reference INFORMATION_SCHEMA.COLUMNS from a linked server? When I try doing that, I g...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones