SQL Server: Should I Use Information_Schema Tables Over Sys Tables

SQL Server: should I use information_schema tables over sys tables?

I would always try to use the Information_schema views over querying the sys schema directly.

The Views are ISO compliant so in theory you should be able to easily migrate any queries across different RDBMS.

However, there have been some cases where the information that I need is just not available in a view.

I've provided some links with further information on the views and querying a SQL Server Catalog.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

http://msdn.microsoft.com/en-us/library/ms189082.aspx

TSQL INFORMATION_SCHEMA.COLUMNS VS sys.columns VS COL_LENGTH('Table','ColumnName')

SQL Server stores metadata about your database and its contents. These are typically accessed via sys. tables and information_schema. views.

The sys tables are somewhat specific to SQL Server, and tend to be fairly normalised. In contrast, the information_schema views (like other many reporting views you may create) provide data in a more readable format - they often join various sys tables together to get their results. See Difference between Information_schema vs sys tables in SQL Server for some more info.

COL_LENGTH() is a function that operates on the database, and doesn't need to 'read data' as such.

However, for all practical purposes, you will find zero difference between these. If you're just after the column length of a specific column, use COL_LENGTH, as it will probably be marginally faster. Otherwise feel free to use the information_schema views as they provide more easy-to-read information (or a custom set of sys tables joined together) as the number of reads to get the metadata is very small.

For example, I have a table I use for testing called 'test' with 5 columns (ID, and col2, col3, col4, col5). It has almost 2 million rows, but none of the data in that table actually needed to be read - just the metadata.

I ran the commands to get the column lengths/info from each. Each took 0.000s to complete (e.g., less than 1 millisecond). Here are the commands and results (first 10 columns only) to demonstrate some of the differences.

SELECT col_length('dbo.test', 'col2') AS Col2_info
  /*
  Col2_info
  100
  */

SELECT * FROM sys.columns where object_id = (SELECT TOP 1 object_id FROM sys.objects WHERE name = 'test')
  /*
  object_id   name  column_id  system_type_id  user_type_id  max_length  precision  scale  collation_name        is_nullable  is_ansi_padded
  2094630505  ID    1          56              56            4           10         0      NULL                  0            0
  2094630505  col2  2          167             167           100         0          0      Latin1_General_CI_AS  0            1
  2094630505  col3  3          167             167           100         0          0      Latin1_General_CI_AS  1            1
  2094630505  col4  4          167             167           100         0          0      Latin1_General_CI_AS  1            1
  2094630505  col5  5          167             167           100         0          0      Latin1_General_CI_AS  1            1
  */

SELECT * from information_schema.COLUMNS where table_name = 'test'
  /*
  TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  COLUMN_NAME  ORDINAL_POSITION  COLUMN_DEFAULT  IS_NULLABLE  DATA_TYPE  CHARACTER_MAXIMUM_LENGTH
  Testdb         dbo           test        ID           1                 NULL            NO           int        NULL
  Testdb         dbo           test        col2         2                 NULL            NO           varchar    100
  Testdb         dbo           test        col3         3                 NULL            YES          varchar    100
  Testdb         dbo           test        col4         4                 NULL            YES          varchar    100
  Testdb         dbo           test        col5         5                 NULL            YES          varchar    100
  */

Note in the version above, the sys.columns version was a) harder to construct, as it was only related to the object_id of my test_table; also it provides data that is a lot less easily readable than the information_schema version.

INFORMATION_SCHEMA vs sysobjects

The INFORMATION_SCHEMA is part of the SQL-92 standard, so it's not likely to change nearly as often as sysobjects.

The views provide an internal, system table-independent view of the SQL Server metadata. They work correctly even if significant changes have been made to the underlying system tables.

You are always much better off querying INFORMATION_SCHEMA, because it hides the implementation details of the objects in sysobjects.

SQL Server how to select from a list of tables using information_schema.columns or sys.tables?

Assuming I understand the question, you want to select all the values of a specific column from all the tables where this column exists.

For that, you need to use dynamic SQL.

Here is one way to do it:

DECLARE @Sql nvarchar(4000) -- You might need nvarchar(max) here

SELECT @Sql = STUFF(
        (       
            SELECT ' UNION ALL SELECT Id, '''+ TABLE_NAME +''' As TableName FROM '+ TABLE_NAME
            FROM Information_schema.Columns
            WHERE Column_Name = 'ID'
            FOR XML PATH('')
        ), 1, 11, '')

EXEC(@Sql)

This will return all Id values from all tables where there is an Id column, along with the table name where it exists.

Please note that it requires that all the id columns will have the same data type, or at least data types that can be implicitly converted to each other.

How does a table not being in information_schema or systables happen?

@DeadZone was right on the money, the query had some issues.
I was using:

DECLARE @command varchar(1000) 
SELECT @command = 'SELECT * FROM sysobjects WHERE xtype=''U'' ' 
EXEC sp_MSforeachdb @command

But it would only show system tables. So then I switched to a more direct query to see what was going on and was able to view the tables:

use MYDATABASENAME;
SELECT * FROM sysobjects WHERE xtype='U'

Inner join with system tables

sys.triggers joins to sys.tables on sys.triggers.parent_id, eg

select t.name, trig.name trigger_name
from sys.tables t
join sys.triggers trig
  on trig.parent_class = 1
 and trig.parent_id = t.object_id

There are no my tables in INFORMATION_SCHEMA.TABLES. Why?

You are using master database.
Choose your database by USE

USE [YourDAtaBaseName]
SELECT * FROM INFORMATION_SCHEMA.Tables

---

Related Topics