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
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
I’m trying to geat a list of tables in all views in a DB including the views that have their source tables in another database…
SELECT * FROM [INFORMATION_SCHEMA].VIEW_TABLE_USAGE only gives me a list of views that have their input tables sourced from the current database
this is too good……………
Anyone find a solution to this one, trying to restrict sys, and information_schema objects from ODBC and ms sql managment console on sql server 2008 r2 dbase but having no luck.
found –
but they never had a full proof answer either from 3 yrs ago. Help please as I am trying to set up the connection to the dbase where the user will only be able to see a select set of tables, views and with either userid pass word via ms sql mang console or ODBC have just select access to tables for users, keeping things locked down from the user. any ideas?
Brett Stutzman
I require all the databases whether it is attached to Server or not. Is it possible ? Help me
I have one great doubt , i need to search data in a multiple table . the table name’s are like TR42012,TR52012,TR62012 ETC… I give an Input “month” only, that input will check all the table like (TR42012,TR52012,TR62012 ETC…) and give the particular “month” value only….
its possible or not… please give me a solution to send my mail Id ::: “vinosh.john@gmail.com”