This is very simple and can be achieved using system table sys.tables.
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
This will return all the tables in the database which user have created.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
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.
448 Comments. Leave new
sir i want know about how to delete same name table from multiple database .
Dear All,
I have a database with three users and I need to show only tables that related to a particular user instead of showing all tables. So how can I achieve this?
Any help must be appreciated.
Regards,
Jayaram.
Exact select query in sql,
select *from tab;
and
sql server is
select *from sys.tables
Hi All,
I want know about how to db backup via command line.
or i need DB DDL query.
It was great meeting you at PASS; once again, I searched the web for a quick answer, and yours was the first that popped up. As always, you’re a wonderful resource.
-Jennifer
Thank you so much!
I am now following you as well!
Hi dave,
i want to thank you very much for this useful blog. keep helping us !
between brackets : are one of my references.
I suggest if we add “where is_ms_shipped = 0” will return tables that user created, because i found that only select * from sys.tables returns all tables (even tables that was created by replication or sync process etc ..)
regards
Alternatively, you can query information_Schema.tables view to access the user defined tables so that you dont need to query on system tables directly
Hi,
I connected sql server through sql developer, in that i am not able see some table while querying like “SELECT *FROM TEST” ,its giving invalid object.but if select TEST table by clicking on TEST object its showing all data and structure.Please help in this regard thanks.
Make sure you have used the correct object owner name
Hello Pinal / Madhivanan,
First up, EXEC SP_MSFOREACHDB does not work on my DB runnin SQL 2k8 R2.
Second, EXEC sp_help ” returns the schema & metadata pertaining to one table at a time. Is there a way to put this in some kind of LOOP, or iteration & execute it against all the Tables within the DB at the same time ?
Lastly, why is it so that SELECT * FROM SYS.TABLES returns an Error like Object not found, whereas, SELECT * FROM sys.tables, executes successfully ? Why is the Query parser Case-Sensitive … ??
Any suggestions ?
Regards,
Aashish Vaghela.
The database seems to be a case sensitive. What is the error you got when you triede EXEC SP_MSFOREACHDB ?
Hi, Can you post the query for SQL server to get the active objects?
how can we delete total tables which are not used from start or which doesnt have data ?
how can we delete total tables which are not used from start or which doesnt have data ?
in a single server
How to Create a stored procedure which takes ‘database name’ and table name as input ‘parameters’ and return column name list of that table.
This is subject to sql injection and use it carefully
declare @sql varchar(1000)
set @sql=’select column_name from ‘+@dbname+’.information_schema.tables where table_name=”’+@table_name+””
exec(@sql)
can we see the data of offset table
i want all table names from the database. please let me know the syntax
select * from user_objects where object_type = ‘TABLE’;
Better still –
select object_name from user_objects where object_type = ‘TABLE’;
Hello EveryOne,
I want to get the Table Values of respective Project of QC from SQL Server 2005, which Contains n number of Projects.
Can any one tell me the query for that ??
how can I get all table names with its record count in one query
Hi,
I have one SP and i want to know the dependency in all databases i.e wher the sp is using.
Sp_syscontents ‘store_proc_name’ is fine or any other sql statment?
Please reply.
Thanks
This command may help to list all columns, all tables and for all databases in SQL server.
use master
go
DECLARE @CMD VARCHAR(1000)
create table #TableNames ( DatabaseName sysname, TableName sysname, ColumnName sysname, DataType sysname, Length sysname)
SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, SysObjects.[Name] as TableName, SysColumns.[Name] as ColumnName,
SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns
ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]
WHERE SysObjects.[type] = “U” ORDER BY SysObjects.[Name]’
insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames Where DatabaseName not in ( ‘master’,’msdb’, ‘tempdb’, ‘model’ )
drop table #TableNames