Thursday, August 21, 2008

Rusty on the ol' SQL

It took me a good 15 minutes to figure out how to get a list of table names with schema's from sql 2005..jeez. not good.
I would not be surprised if its actually wrong. Anyway, whats with hiding the sys tables? Weird.

select ss.name 'schema', so.name 'table'
from sysobjects so
inner join sys.schemas ss
on so.uid = ss.schema_id
where so.xtype = 'U'
order by ss.name, so.name

1 comment:

Lee Campbell said...

Select * from INFORMATION_SCHEMA.TABLES;
Go on hit F1. SQL Server Books OnLine is great. I wouldn't say that the INFORMATION_SCHEMA.TABLES view is hidden. It in the object explorer under Views\System Views.

:p