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
I can get a list of all stored procedures that I’m using in my Db via this query:
SELECT * FROM SYS.PROCEDURES WHERE [Name] LIKE ‘usp%’ ORDER BY [Name];
(I’m looking for the name to be like ‘usp’ because that’s the prefix to all of the stored procedures I’ve created.)
What I’m looking for is where the text of the stored procedure is located. For example, I need to drop the use of a table that is causing performance issues (they’re using labor intensive triggers) and want to search the text of all stored procedures I’ve created for the table name so I know which stored procedures need changing. There are over 130 stored procedures and looking through every one of them seems too time consuming.
It would be nice to run a query that would do the search for me…like this
SELECT * FROM SYS.SomeSystemTable s INNER JOIN SYS.PROCEDURES p ON s.object_id = p.object_id WHERE [Name] LIKE tblBadTableName%’ .
Any Ideas?
Hi,
Could you please let me know how to retrieve the deleted table list in SQL.
Thanks,
Suba
There is no direct way until you have a copy of backup taken before tables were deleted
Hi,
How can i know that how many times my tables,views and/or functions have been executed or used by the users?Can anybody help me or can give me some idea ,plz?
Hi,
Is it possible ???
how to select data when you dont know the table name, only thing I know is database where the column resides
like I have to find how many CID columns are in my database and in which table they are resides???
Please do help me … because I have to work on Large number of database tables ….
Hi,
I want to list of my database tables’ column name with their Datatype.
Can you tell me how to do that?
Regards,
Pooja
I use this to get all Table and Sprocs on all Database on a server:
sp_msforeachdb @command1=”USE ?;select name, type from sys.objects where type in (‘U’, ‘P’) and name not like ‘dt%’ and name not like ‘#%’ order by type”
Note sp_msforeachdb has a limits the query to 128 digits.
This code shows all the tables and sprocs for each DB, the size of each table int he DB and the DB file sizes:
sp_msforeachdb @command1=”USE ?;select ‘?’,name,type from sys.objects where type in(‘U’,’P’) and name not like ‘dt%’ and name not like ‘#%’ order by 3″
, @command2=”exec sp_MSforeachtable @command1 = ‘exec sp_spaceused'”
, @command3=”exec sp_helpfile”
GO
i want default databases in sql server and their tables like EMP table in oracle . plz help me
hye,
i want to modifier a field in a database and this field is related to many database what is the code to do to modifier this field in all database at the same time
best regards
Everyone that contributed to this site has helped me tremendously. Thanks for leaving your informational note. This blog Rocks who do i give the credit to?? thanks again.
Hi pinal,
i need to write the Use statement inside the stored procedure.
but it throws error.
Is there any alternate way for this?
let me make it more clear.
i have created one stored proc in master database which takes database name as parameter.
and i want to execute the code on the respective database.
How can i achieve this?
Thanks,
Anirudha ( SQL -Developer)
Grat tips, tanks a lot !!
Hi Mr.Pinal,
In SQL server 2000 i got the error message.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.Tables’.
Please help on it.
Regards,
Krishna
You need to use
select * from sysobjects
where xtype='u'
What should i do to become the future C.E.O of S.Q.L
First try to become a M.V.P. of SQL Server 2k5 / 2k8, like Pinal Dave. CEOs will consult you anyways !
@ Krishna,
In SQL Server 2000, there is no view called ‘sys.tables’. This view is only in SQL Server 2005.
You can use below quiries to get user defined tables names in SQL Server 2000.
select * from sysobjects where table = ‘u’
or
select * from information_schema.tables
Regards,
IM.
HI Imran Mohammed,
Thank you for your information…..
Regards,
Vidhya
In SQL Server 2000 this works well to return all User Tables:
SELECT Name
FROM FOTNAudit.dbo.sysobjects
WHERE xtype = ‘U’
ORDER BY Name
FOTNAudit is a specific db name. Replace with whatever you need to use.
Hello sir
How can get all the table list from Sql Server 2005.
Thanks & Regards
Jignesh Patel
@Jignesh Patel
This script will give you tables in all database ( meaning in whole SQL Server )
DECLARE @CMD VARCHAR(1000)
create table #TableNames ( DatabaseName sysname, TableName sysname)
SET @CMD = ‘USE [?]; SELECT DB_NAME()DATABASE_NAME, NAME FROM SYS.Tables’
insert into #TableNames EXEC SP_MSFOREACHDB @CMD
SELECT * FROM #TableNames — Where DatabaseName not in ( ‘master’,’msdb’, ‘tempdb’, ‘model’ )
drop table #TableNames
If you dont want system databases table names, then you need to remove — ( remove comment symbol) from above script, otherwise it will include system database table names also.
Regards,
IM.