[sql] T-SQL Quiz
Question:
A user executes the following two queries in SQL Server Query Analyzer. Query 1 returns no rows; Query 2 returns one row. Why?
--QUERY 1
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ExampleTable'
AND COLUMN_NAME = 'Description'
--QUERY 2
SELECT * FROM sysobjects, syscolumns
WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'ExampleTable'
AND syscolumns.name = 'Description'
Note: This is of importance if you are using the clause in an insert script:
IF NOT EXISTS (SELECT * FROM sysobjects, syscolumns WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'ExampleTable'
AND syscolumns.name = 'Description')
alter table dbo.ExampleTable add Description varchar(50)
GO
My Answer:
The INFORMATION_SCHEMA.COLUMNS view checks permissions and only shows columns accessable to the current user. INFORMATION_SCHEMA lives in the master database, so the user's rights may be much more restricted on this view. sysobjects and syscolumns live in the current database.
Bottom line: Users may have broader rights under the systables than the INFORMATION_SCHEMA views.