Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Quiz

Status
Not open for further replies.
ptheriault

ptheriault

IS-IT--Management
Aug 28, 2006
2,699
US
You guys may know this but I didn't. I stumbled accross this last while searching for Joe Celko's latest book.

A user executes the following two queries in SQL Server Query Analyzer. Query 1 returns no rows; Query 2 returns one row. Why?

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

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Sort by date Sort by votes
Views don't show up in Information_Schema.Columns but they are in the sysobjects and syscolumns table. Therefore, the table wouldn't exist and the alter table command would fail.

IF NOT EXISTS (SELECT * FROM sysobjects, syscolumns WHERE sysobjects.id = syscolumns.id
AND sysobjects.name = 'ExampleTable'
AND syscolumns.name = 'Description'
AND sysobjects.xtype = 'U')
alter table dbo.ExampleTable add Description varchar(50)

By adding the xtype check, you can make sure to ONLY return user tables.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Ahhhh.... that was wrong.

The real answer is...

Views and tables are returned by information_schema.columns but other objects are not, so, for example, you could have a table value function named 'ExampleTable' that returns a column named 'Description'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,

your second answer is excellent. I don't think I would have thought of that. But there is also another possibility that we as DBA's don't often run into.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
IIRC, Indexes are found in sysobjects but not INFORMATION_SCHEMA.COLUMNS. Constraints are found in INFORMATION_SCHEMA.COLUMNS, but if you are after an index you won't find it there

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Hmmm?

Here is the answer that Jon Galloway provided...

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



At first I took his answer at face value.

However, After a little research I believe he is incorrect.

INFORMATION_SCHEME does check user permissions and will not show a column if a user doesn't have access to it. But
I found that sys.objects also checks permissions.


I created a login named test and granted test access to the adventure works database. (only as a member of the
public role)

I then ran the following querries.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SalesOrderDetail'

SELECT * FROM sysobjects
WHERE name = 'SalesOrderDetail'

I expected query 1 to not return any data, but both didn't return anything.

I then granted select permissions on sys.objects to test. Still no records.

I also tested these results on SQL 2000.

So At this point I believe that Georege's answer is the most likely cause.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Is SalesOrderDetail a view?

And can someone tell me please what is the benefit of using INFORMATION_SCHEMA views? I decided I should learn to use them because it seemed to be what everyone else in here was doing (I usually just query the system tables directly), but of course the first time I tried was when I wanted to drop and recreate an index (which of course led me to discover the 'problem' noted above). I would guess that the benefit is you don't have to give your users read access to the system tables, but I think a real dba could probably clarify this much better for me?

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
>>I usually just query the system tables directly
You may not know this but sys.objects and other system tables in user databases are really just views now.

I find INFORMATION_SCHEMA views much easier to use. For example if I need to find a strint in a stored procedure I use this. I get back a list of procedures that referrence CLAIM_COUNT

Code: 
select *
from INFORMATION_SCHEMA.ROUTINES  
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME like 'usp%'
AND ROUTINE_DEFINITION like '%CLAIM_COUNT%'

Not all objects are in INFORMATION_SCHEMA like sys.indexes and sys.triggers


- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Alex,

I prefer to use the information_schema views for a couple reasons.

1. If the information you are looking for is in the view, then you're done. There's no need to look any further.

2. Microsoft claims that the views will consistently return the same information from version to version, whereas system tables may change from time to time.

Want to have some real fun?

1. Open Query Analyzer.
2. Show the object browser (press F8 if it's not already showing).
3. Expand the master database
4. Expand Views
5. Right click on Information_Schema.Columns
6. Click 'Script object to new window as' -> 'Create'

You will see the code that is used to create the view.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Alex,
If you can Right click and script the object. (I don't have that option)

try this
Change your query output to text first

sp_helptext 'INFORMATION_SCHEMA.COLUMNS'

>>Click 'Script object to new window as' -> 'Create'
George,
Does that work for you?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
By the way Alex,

SalesOrderDetail is a table.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Yes, but only in Query Analyzer. It does not work in 2005's Management Studio.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Oh, OK. I tried it from SSMS.
Alex,
Try this too.


sp_helptext 'sys.objects'

You will see that sys.objects is a view too.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
I do see now why you guys are using them, the question becomes 'why are indexes not included in any of the pre-packaged views'? Would it not be a good thing to have available?

Maybe I should just create my own view to show indexes and call it alex_schema_indexes. But for now I will keep querying the system tables. I don't have very many interesting things to do at work these days, so I need every bit of work that I can find :-(

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
George,
What do you think of Jon's answer? Do you think it's possible for one to work and one not too. I think if one query works then they both would work.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul be very carefull with that

Code: 
create procedure usp_claims
as
----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
select getdate()
print 'CLAIM_COUNT'
go

now run this

Code: 
select *
from INFORMATION_SCHEMA.ROUTINES  
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME like 'usp%'
AND ROUTINE_DEFINITION like '%CLAIM_COUNT%'

see? nothing is returned

now let's make the proc smaller

Code: 
alter procedure usp_claims
as
----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------
select getdate()
print 'CLAIM_COUNT'
go


and try again
Code: 
select *
from INFORMATION_SCHEMA.ROUTINES  
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME like 'usp%'
AND ROUTINE_DEFINITION like '%CLAIM_COUNT%'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions





 
Upvote 0 Downvote
[sadeyes] Why would it do that?

By the way guys.

Do you think Jon is correct?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
I think it is correct, the key word being MAY. It all depends on how your permissions are set up. Of course, I am not a dba I am just expected to function as one.

Ignorance of certain subjects is a great part of wisdom
 
Upvote 0 Downvote
Ah, Denis you are wise beyone your years! [yoda]

ROUTINE_DEFINITION
nvarchar(4000)




- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
529
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
516
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
321
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
288
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
369
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top