SQL SERVER – List Schema Name and Table Name for Database

Just a day ago, I was looking for script which generates all the tables in database along with its schema name. I tried to Search@SQLAuthority.com but got too many results. For the same reason, I am going to write down today’s quick and small blog post and I will remember that I had written I wrote it after my 1000th article.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – 1000th Article Milestone – 8 Millions Views – Solid Quality Mentors
Next Post
SQL SERVER – Why You Should Attend PASS Summit Unite 2009- Seattle

Related Posts

32 Comments. Leave new

  • You should use QUOTENAME for this – as that is what it is intended for

    SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ‘.’ + QUOTENAME(name)
    AS SchemaTable
    FROM sys.tables

    Reply
  • RBarryYoung
    June 18, 2009 5:59 am

    This works too, and I find it easier to remember:

    SELECT ‘[‘+TABLE_SCHEMA+’].[‘+TABLE_NAME+’]’
    FROM INFORMATION_SCHEMA.TABLES

    Reply
  • I have one problem with linked server.

    I had create a link server on SQL 2005 server which point to SQL 200 server.
    When i am trying to run select query using this linked server it gives error “invalid column error”

    SQL 2005 collation Binary
    SQL 2000 Server default

    SELECT PROJECT_ID
    from CNVTEST.TEST.DBO.TESTTABLE

    this will give error
    but if i am run

    SELECT project_id from CNVTEST.TEST.DBO.TESTTABLE
    it will run perfect.

    I want that query will run with Upper case

    Please provide solution for this problem

    Thanks in advance

    Reply
  • Try this:
    sp_msforeachtable @command1=’print ”?”’

    It’s a very useful system stored proc, and can be used for a multitude of purposes, applying sql to each table in a database. There are also options to run pre and post sql statements before looping through each table, as well as adding a where clause to filter out tables you do not want.

    Microsoft claim it’s an internal, unsupported API and therefore do not provide documentation for it, but it’s mighty useful so you can find ‘unofficial’ documentation out there. Alternatively, you can open up the stored proc from the Master db and have a look…

    Reply
  • For those users of SQL Assistant, this modification is useful to show your schemas :

    if @@version like ‘Microsoft SQL Server 2005%’

    UNION ALL
    SELECT DISTINCT
    SCHEMA_NAME(schema_id),
    ‘SC’,
    schema_id
    FROM sys.tables

    Reply
  • another way for Sql Server 2000

    select quotename(User_Name(uid))+’.’+ quotename(Object_Name(id)) as [Schema Table] from sysObjects where xtype = ‘U’

    Reply
  • Wow! that’s exactly what I was looking for!!
    Thanks!

    Reply
  • plz tell on whom i shall give the name of server

    Reply
  • srivalli.M.
    June 13, 2011 5:26 pm

    hi, i have the list of tablenames with some properties.
    i have joined those tables. now i wanted to know which table contains what properties.

    Reply
  • Select distinct table_name from INFORMATION_SCHEMA.Columns
    This will give the list of tables in the database

    Reply
  • It is showing…
    “Unable to start the Transact-SQL debugger. The Transact-SQL debugger does not support SQL Server 2005 or earlier versions of SQL Server. (SQLEditors)”

    Is there any other way?? Plz tell me..

    Reply
  • Thanks ,Its excactly what I needed.

    Reply
  • Hi pinal,

    i have to schedule backup of particular schema objects, using sql server agent jobs.

    in database there is five schema like
    [a].tbl_a
    [a].usp_a
    [a].fn_a
    [b].tbl_b
    [b].usp_b
    [b].fn_b

    where [a] and [b] are the schemas.

    now i have to schedule full backup of only [a] schema objects.
    and there is not any secondary file group for database.

    is it possible or not?

    please tell me.

    Reply
  • Thanks!

    Reply
  • Hi Pinal

    I have a sql server 2008 setup where my application creates schemas in database.

    I want to know the creation date of the schemas created under security>>schema.

    Is there any way to find it out.

    Regards
    Rohit

    Reply
  • Bill Fennell
    March 28, 2012 7:41 am

    Just what I needed, when I needed it, Pinal! You da man! Thanks!

    Reply
  • SELECT Tables.Name FROM sys.tables

    Reply
  • I have created one schema, but not having any tables in the schema. we may not get those details here in the above queries. can any one help me how to get the schema name if the schema not contains any table.

    Reply
  • I am fairly new to Sql Server. How can you query tables on another schema without the database prefix? For example i have one user called user1 which has access to 2 schemas called schema1 and schema2 where schema1 is the default one. In the default schema i can query a table as select * from schema1.tablename but if i want to query the other schema i need to write it as select * from schema2.schema2.tablename. Is there a way to write this as select * from schema2.tablename instead?

    Reply
  • Mya Yadanar Soe
    August 21, 2013 8:15 am

    To Pinal Dave, Thank you for your blog.

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version