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