How can I query the list of FULL table names in SQL server database

Question

I have a need to iterate over all of the tables in my database. I am doing so with thsi query:

SELECT so.name, so.*
FROM sysobjects so
WHERE so.xtype = 'U'

This works fine for all of the tables in the 'dbo' schema, but it doesn't work for any other schemas. I want the output to be like this:

Users.Address Users.TelephoneNumbers dbo.GlobalSettings dbo.Configuration

Instead I get this:

Address TelephoneNumber GlobalSettings Configuration

When I try to construct dynamic SQL queries, it fails because it can't find the Address and TelephoneNumber tables because their schema is not the default 'dbo'.

Any ideas?

Answer 1

How about:

Select Schema_Name + '.' + Table_Name from Information_Schema.Tables

Answer 2

In SQL Server 2005 and up, you should no longer use the deprecated sysobjects and sytables and these views.

Use the "sys" catalog views instead:

SELECT * FROM sys.tables

should work on other than the "dbo" schema as well.