INFORMATION_SCHEMA: A Quick Reference Guide

All about Information Schema in SQL Server

Introduction:
Are you tired of spending hours writing complex SQL queries to retrieve information from your database? INFORMATION_SCHEMA is a powerful tool that can simplify your tasks and provide valuable insights into your database structure. In this article, we’ll explore various scenarios where INFORMATION_SCHEMA can be used to answer common database questions.

Note: Queries are CASE-INSENSITIVE

Scenario 1: Listing All Tables
To get a complete list of all tables in your database, you can use the following query:

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_CATALOG='<PUT_YOUR_DATABASE_NAME>';

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_CATALOG='OrgData';

This query will return a list of all table names in the “OrgData” database.

Scenario 2: Viewing Column Information
To obtain detailed information about the columns in a specific table, use the following query:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'OrgData'
AND TABLE_NAME = 'Employees';

This query will display the column names, data types, and maximum character lengths for all columns in the “Employees” table.

Scenario 3: Counting Columns
To determine the total number of columns in a table, you can use the COUNT() function:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'orgData'
AND TABLE_NAME = 'Employees';

This query will return the number of columns in the “Employees” table.

Scenario 4:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema_name'
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND CHARACTER_MAXIMUM_LENGTH = 255;

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND CHARACTER_MAXIMUM_LENGTH = 255;

This query will return all the table names, column names, data type for “dbo” schema where maximum column length is 255.

Additional Scenarios:

• Listing Views: SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG = 'your_database_name';
• Listing Stored Procedures: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE';
• Checking Column Data Types: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'TEXT';

Best Practices:

• Always use INFORMATION_SCHEMA for metadata queries instead of hardcoding table or column names.
• Consider using sys views for more performance-oriented operations.
• Be aware of potential schema differences between different SQL Server versions.

I hope these suggestions help you improve your article and make it more valuable to your readers!