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

SQL SERVER - List Schema Name and Table Name for Database schematable

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

SQL Scripts
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

  • Murali Mannava
    March 25, 2014 9:43 pm

    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.

    Reply
  • This will work too…

    SELECT SCHEMA_NAME,NAME
    FROM INFORMATION_SCHEMA.SCHEMATA i
    INNER JOIN SYS.TABLES s
    ON i.SCHEMA_NAME = SCHEMA_NAME(s.SCHEMA_ID)
    ORDER BY SCHEMA_NAME

    Reply
  • select * from INFORMATION_SCHEMA.TABLES

    select * from INFORMATION_SCHEMA.COLUMNS

    The above statements will give the same information using single statements

    Reply
  • select * from INFORMATION_SCHEMA.TABLES
    select * from INFORMATION_SCHEMA.COLUMNS

    Reply
  • Hi,

    I have created a linked server named like Test. It contains around 20 tables.
    I want to list out tables in linked server in sql server. Can you please let me know how to do?

    Thanks,
    Murali

    Reply
  • I am running something similar (see below). I use this to help me find what tables a field is in, however I noticed that a field/table combo that exists in the database does NOT appear when I run this, why is that? I also just looked for the table in sys.table and it doesn’t appear, it is a custom table, could that be the issue, do you have to run something to get tables to appear in sys.table?

    SELECT
    t.name AS table_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE ‘insert field name’
    ORDER BY schema_name, table_name;

    Thanks everyone!
    Jill

    Reply
  • Is there any way i can know who created Schema, in my Db someone created Custom schema and I want to know who did that >>

    Reply
  • Hello, is there any way to select all tables from all databases inside the same server?

    Reply
  • MODERAGE C WAAS
    August 31, 2021 7:44 pm

    Create table #yourcolumndetails(
    DBaseName varchar(100),
    TableSchema varchar(50),
    TableName varchar(100),
    ColumnName varchar(100),
    DataType varchar(100),
    CharMaxLength varchar(100))

    EXEC sp_MSForEachDB @command1=’USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ”origin”’

    select * from #yourcolumndetails
    Drop table #yourcolumndetails

    Reply

Leave a Reply