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
plz tell me the query for getting information about all views in sql server
Can some one help me in creating a new table in the database where you have only master table?
Hello Pinal,
Can you help me how to find out the usage of the tables on the databases, and its growth on the tables from period to period…
to collect the tables that are heavily used…
Regards,
Rohit
EXEC sp_spaceused ‘table_name’
Thanks for the prompt response Madhivanan….
But it gives only row count, used and unused information only, that too it happening me to do on each and evry table as there are lot many tables in each of my databases.
So is there any process to look at a time on whole database that too from time to time period, so that I can figure out the growth in the tables and which are heavily used…
For example: suppose there is 100kb of used data on a table and after two days it may be something more like 150kb… so I want that growth from time to time so that I can aware of getting the information which are heavily used…
I hope u got understood…. if not let me know, I’ll try to explain little more abt my issue…
Regards,
Rohit
Hello,
Can anyone help me quickly how to format this datepart(week,date) to two characters length… I mean if the work week is one then it has to mention as 01 not as ‘1’… in the same way if the work week is 2 then it has to mention as 02 instead of 2…. and need to go on till 09….
Regards,
Rohit
Hi Pinal,
can you let me know where am doing wrong in this stored procedure….
Actually if I run this query its working fine in 4 seconds of time…
BEGIN
SET @startdate = (SELECT CONVERT(VARCHAR(10),date_begin,101) FROM ww_calendar
WHERE ww = SUBSTRING(@wwstart,5,LEN(@wwstart)) AND year = SUBSTRING(@wwstart,1,4))
SET @enddate = (SELECT CONVERT(VARCHAR(10),date_end,101) FROM ww_calendar
WHERE ww = SUBSTRING(@wwend,5,len(@wwend)) AND year = SUBSTRING(@wwend,1,4))
INSERT INTO @temp_sn
SELECT * FROM sntrax_current..SplitString(@snlist, ‘,’)
DECLARE c_sn CURSOR FOR
SELECT * FROM @temp_sn
OPEN c_sn
FETCH next FROM c_sn INTO @sn
WHILE (@@fetch_status -1)
BEGIN
SET @sn = ltrim(rtrim(@sn))
SET @sqlQuery = ‘INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(”0” + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >=”’+ @startdate + ”’AND
shpm.shipdate = @startdate AND
shpm.shipdate <= @enddate AND
Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc
Kindly help me in this query pls….
Regards,
Venkat
oops sorry….
the above sent query was working fine in 4 to 6 seconds, but if I remove the query out of @sqlquery parameter it taking much time (approximately 3 to 5mnts of time)
INSERT INTO #temp SELECT CAST(Datepart(year,shpm.shipdate)as varchar) +
RIGHT(‘0’ + CAST(Datepart(week,shpm.shipdate)as varchar),2) as workweek,
Substring(snm.sn, 1, 4) AS fs4_sn,snm.sn as sn,
CONVERT(VARCHAR(10),shpm.shipdate,121) as ship_date FROM sntrax_current..sn_master snm
LEFT OUTER JOIN sntrax_current..sn_ship_link shplnk
ON snm.sn_identity = shplnk.sn_identity
LEFT OUTER JOIN sntrax_current..ship_master shpm
ON shplnk.ship_identity = shpm.ship_identity
WHERE shpm.shipdate >= @startdate AND
shpm.shipdate <= @enddate AND
Substring(snm.sn, 1, 4) = @sn order by shpm.shipdate desc
can you help me where am doing wrong pls….
Regards,
Venkat
Still waiting some one answer to my query pls….
To list all tables from existing database use sql command “select * from tab” without double quotes.
Hello Pinal,
Expecting an answer from you about my query….
Actually why its showing such an impact on that parameter… when I run the query by initializing the query into parameter “Set @sqlquery = …” its working fine giving output in seconds… but when I take out of the parameters and run directly its taking much time… why there is such a difference in run time….???
Looking for reason from you… and help me with a correction in my query….
Regards,
Venkat
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = ‘U’
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 ASC
By Thameem
Hi Pinal Dave,
Greetings. I have a problem in creating a query. Can you please see and provide some help.
Scenerio is:
id column_name parentid
1 A 0
2 B 1
3 C 1
4 D 3
5 E 2
6 F 4
7 G 0
The columns which has parentid as 0 are the top most items and rest all have the parent-child relation.
I want it somelike
A
B
E
C
D
F
G
Where B,C are child of A, E is child of B, D is child of C an so on.
Thanks in advance….:)
I would recommend — select * table from information_schema.tables — as well. It works pretty much good.
Column ‘a’
—————————————————–
Select e.empno,d.deptname from emp e
inner join dept d on e.deptno=d.deptno
Select e.empname,ed.salary from emp e
inner join empdet ed on e.empno=ed.empno
The above are two rows in column named as ‘a’ in a table ‘t1’
Now i need the result to be as
column ‘Obj_names’
——————————————-
emp
dept
emp
empdet
(ie), I need a query that will exactly get the table names from those SQL statements and display me..
IS THERE ANY OPTIONS FOR THIS..
Help me PINALDAVE or ANYONE who can ??????????
Regards,
Arunkumar
MCTS – SQL Server 2005
I dont think there is an option to do this. Why do you want this? You can parse the statement and get the table names
Hi Pinal Dave,
Is it possible to execute a query in two database together?
For example :
select database1.Name from sys.procedures where [name] not in (select database2.Name from sys.procedures)
Okay I found the solution
Here we go:
Select t1.Name from
db1.sys.procedures t1 where t1.name not in (Select t2.Name from
db2.sys.procedures t2)
Hope it helps someone….
Pinal Dave, you are doing fabulous job man. keep going..do well….
Regards
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