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)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
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
Very good point Simon.
Thank you, this works fine and the original post not work for me – sys.tables is not recognized.
This works too, and I find it easier to remember:
SELECT ‘[‘+TABLE_SCHEMA+’].[‘+TABLE_NAME+’]’
FROM INFORMATION_SCHEMA.TABLES
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
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…
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
another way for Sql Server 2000
select quotename(User_Name(uid))+’.’+ quotename(Object_Name(id)) as [Schema Table] from sysObjects where xtype = ‘U’
Wow! that’s exactly what I was looking for!!
Thanks!
plz tell on whom i shall give the name of server
Kindly tell us ur need correctly…
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.
Select distinct table_name from INFORMATION_SCHEMA.Columns
This will give the list of tables in the database
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..
Thanks ,Its excactly what I needed.
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.
Thanks!
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
Just what I needed, when I needed it, Pinal! You da man! Thanks!
SELECT Tables.Name FROM sys.tables
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.
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?
To Pinal Dave, Thank you for your blog.