How to get a table's full name including schema? Thanks.

Question

Post reply

How to get a table's full name including schema? Thanks.

halifaxdal

SSCoach

Points: 19741
More actions
September 2, 2010 at 2:31 pm

#223443

Hi folks,
I need to get a list of all object(View, Function, and Procedure)'s name for a designated database, I need the schema's info, for table, currently I am using:
select * from sysobjects where xtype = 'U' and Left(name, 3) NOT IN ('sp_', 'xp_', 'ms_', 'dt_')
This will give me the name list, however, it doesn't return schema's name, so for example, in AdventureWorks, it returns table Production.ProducProductPhoto as ProducProductPhoto.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

You can join on sys.schemas ...

SELECT s.name + '.' + o.name

FROM sys.objects o

JOIN sys.schemas s

ON s.schema_id = o.schema_id

WHERE o.type = 'U'

AND LEFT(o.name, 3) NOT IN ('sp_', 'xp_', 'ms_', 'dt_')

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

anand_vanam SSCrazy Points: 2376 More actions · Answer 2

SELECT s.name + '.' + o.name

FROM sys.objects o

JOIN sys.schemas s

ON s.schema_id = o.schema_id

WHERE o.type = 'U'

why we need "AND LEFT(o.name, 3) NOT IN ('sp_', 'xp_', 'ms_', 'dt_')" in the WHERE clause

It does not make any difference in the result.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Slightly modified version of that same query

SELECT s.name + '.' + o.name

FROM sys.objects o

JOIN sys.schemas s

ON s.schema_id = o.schema_id

WHERE o.type = 'U'

And OBJECTPROPERTY(o.object_id,'Ismsshipped') = 0

Here, we use Objectproperty to determine which ones are user created.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

anand_vanam (9/2/2010)
SELECT s.name + '.' + o.name
FROM sys.objects o
JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.type = 'U'
why we need "AND LEFT(o.name, 3) NOT IN ('sp_', 'xp_', 'ms_', 'dt_')" in the WHERE clause
It does not make any difference in the result.

Some databases will have the dt_ as a table in the result set. Though it is a table, you probably don't want to include it because it is technically an ms shipped table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events