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?

What version of SQL Server? YOu can use SYSTABLES: msdn.microsoft.com/en-us/library/ms187406.aspx — OMG Ponies
– OMG Ponies, Commented Dec 9, 2009 at 20:39
Is this Sql Server 2000 (I believe it is from the SQL you posted)? — peacedog
– peacedog, Commented Dec 9, 2009 at 20:39

peacedog · Accepted Answer · 2009-12-09 20:41:37Z

4

How about:

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

answered Dec 9, 2009 at 20:41

peacedog

1,43520 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dcp Over a year ago

@OMG Ponies - No, information_schema.tables will work on SQL Server. You can refer to this link: msdn.microsoft.com/en-us/library/ms186224.aspx

OMG Ponies Over a year ago

+1: Sorry about that, I stand corrected & withdraw my earlier statement

peacedog Over a year ago

It's important to give Microsoft credit when they adhere to standards :)

marc_s · Accepted Answer · 2009-12-09 20:42:17Z

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.

answered Dec 9, 2009 at 20:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

Deepu T · Accepted Answer · 2014-03-13 04:45:05Z

0

In SQL Server 2005/2008:

SELECT * FROM information_schema.tables

In SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U'

answered Mar 13, 2014 at 4:45

Deepu T

7247 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related