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:
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.
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.