Retrieve all Tables in a Database as DatabaseName.Schema.TableName

The general syntax of a CREATE TABLE command in SQL Server 2005/2008 is :


    [ database_name . [ schema_name ] . | schema_name . ] table_name

        ( { <column_definition> | <computed_column_definition> }

        [ <table_constraint> ] [ ,...n ] )

    [ ON { partition_scheme_name ( partition_column_name ) | filegroup

        | "default" } ]

    [ { TEXTIMAGE_ON { filegroup | "default" } ]

[ ; ]

Observe the database_name.[ schema_name ].| schema_name.]table_name syntax. Let us say, if you now want to retrieve all the tables in your SQL Server 2005/2008 database in a similar format, then here's how to do so:

USE Northwind


SELECT DB_NAME() + '.' + OBJECT_NAME(object_Id)

+ '.' + SCHEMA_NAME(schema_id) as 'Fully Qualified Name'

FROM sys.tables


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

1 comment:

Madhivanan said...

It is better practice not to query against system tables. Use Information_schema views instead

The same can be done by

select table_catalog+'.'+table_name from information_schema.tables
where table_type='BASE TABLE'