April 07, 2009

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

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


CREATE TABLE


    [ 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


GO


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


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


FROM sys.tables




OUTPUT


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

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'