Getting the "[dbName].[schema].[tblName]" format of a table

Hi all,

Under SQL Server 2005 :

Is there a command that can get me the "dbName.schema.tblName" for a table?

Or a function that does something that look like that.

Dale

Hi Dale,

See INFORMATION_SCHEMA.TABLES view in BOL.

select

quotename(TABLE_CATALOG) + '.' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME)

from

INFORMATION_SCHEMA.TABLES

where

TABLE_CATALOG = 'Northwind'

and TABLE_SCHEMA = 'dbo'

and TABLE_NAME = 'Orders'

and TABLE_TYPE = 'BASE TABLE'

AMB

|||

Is getting the list of table from the INFORMATION_SCHEMA.Tables is a bad programming habit?

I would get the list of table from there since its more complete than the list from sys.tables.

|||

Hi Dale,

I do not think so. There are developers / dba that prefer object catalog views, other prefer information_schema views, etc. Another approach could be:

use northwind

go

select

db_name(),

object_name([object_id]),

schema_name([schema_id])

from

northwind.sys.objects

where

[type] ='U'

and is_ms_shipped = 0

go

AMB

|||I agree with Dale J. As per the MSDN the "INFORMATION_SCHEMA.TABLES" view retruns each table in the current database for which the current user has permissions. But Sys.Tables will return all the table.|||

Hi Manivannan.D.Sekaran,

Seeing just what you are allow to, is called security and not bad practice.

AMB

|||

Yes AMB,

I just agreed the point of DaleJ "getting more data than this view". I didn't say it is a bad practice. (i wont say IDS are bad-practice).

I want to share one point here, the information shcema is the fixed format on the SQL-92 Standard. Whenever the SQL Server's system tables change this views structure never change.(some times it breaks the backward compatibility).

Since it is a SQL-92 standard these quires can be executed on any database (supported by Informix, Oracle, Sybase & our own SQL Server)

sql