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 (http://blog.sqlauthority.com)

About these ads

26 thoughts on “SQL SERVER – List Schema Name and Table Name for Database

  1. 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

  2. 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

  3. 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…

  4. 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

  5. another way for Sql Server 2000

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

  6. 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.

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 11 of 31 Journey to SQLAuthority

  8. 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..

  9. 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.

  10. 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

  11. 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.

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  13. 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?

  14. Hi Pinal,
    Thanks for the blog. I have a question. I would like to get the table name and locks for that particular table. The current locks by a particular user can be found out in sys.dm_exec_sessions
    Do you have any suggests?
    Please let me know.
    Thanks
    Murali.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s