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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Retrieve all Tables in a Database as DatabaseName.Schema.TableName"
  1. Madhivanan said...
    February 3, 2010 at 11:43 PM

    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'

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions