This query shows rows, columns and size for each table, so you can spot the biggest table in database.
USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
276 Comments. Leave new
can anybody solve this query, i have an employee table and i need to show all the data of those Employees who are Emp_Status ‘P’ (PERMANENT) and’B’ (PROBATIONARY)
Is this?
Where Emp_Status =’P’ and Emp_Status =’B’
Hi Pinal
i want to shoot a mail once in a day only through asp.net and
sql can you please tell me whats the query for this
waiting to your reply
Dev
Hi,
Why don’t you configure mail in SQL SERVER and develop a Stored Procedure which will be schedule to run at once a day.
This will reduce effort to maintain your task.
To configure mail in SQL, please refer:
Thanks,
Tejas
Hi i dont know how can configure mail server in sql server
can u please tell me
Dev1
Hello Dev,
Please see my following article:
http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/
Regards,
Pinal Dave
i am using sql server 2000
Great job, works like a charm.
Hello, -How i can search the special characters (char(10),char(13),…) in every table and field in a SQL Server Database ?
Thanks, S.P.
Refer this post and change your search string to char(10) or char(13)
hi pinal,
I have 1 table which contain DateTime, Enployee Number and usetype (used for IN or OUT) column, this is for time and attendance. Now I want to know how many employees are inside the campus and their employee ids. I am trying it with max(intime)>max(outtime) but i m not getting the desired result.
sample data-
emp no occurdatetime usetype (1 for IN,2
for OUT)
2099 2009-01-28 11:25:21.000 1
3652 2009-01-28 11:26:12.000 1
3607 2009-01-28 11:26:13.000 1
6270 2009-01-28 11:26:39.000 2
4489 2009-01-28 11:26:53.000 1
6536 2009-01-28 11:27:00.000 1
1742 2009-01-28 11:27:02.000 2
2579 2009-01-28 11:27:09.000 1
3853 2009-01-28 11:28:00.000 2
5762 2009-01-28 11:29:09.000 1
5668 2009-01-28 11:29:31.000 2
4956 2009-01-28 11:31:43.000 2
and how to convert this datetime data formate to Date and time separately.
Please give me some clue to move further.
thanks
Can you post some sample data with expected result for the above sample data?
Thank you so much! great help
Thank you for this one…
Thanks you. Great query. Works without any trouble.
How do u calculating the columns using above query………
i am working on SQL. i want to set a counter for primary key which has some data input, so later when i add other rows the primary kry should increase automatically.. Primary key is “Prot1”
Nxt should be “Prot2”
Two options
1 Use identity column and when you select it prefix prot with it
2 Use identity column and a another computed column that has the definition of
col as ‘Prot’+cast(idcol as varchar(10))
thanh you. do you mean to say that while definig the col i should name col as
‘Prot’+cast(idcol as varchar(10))?
Yes. it is
Great, very useful blog.
Hi there!,
Here you can find the sql command to get number of COLUMNS in a table….
select TABLE_NAME, count(*) COLUMNS1 from all_tab_columns where owner=’EMP’
GROUP BY TABLE_NAME
ORDER BY COLUMNS1
/
Hi,
I have a requirement like, I want to know the number of columns in a temp table.
Thanks in advance
Madan
Hi,
I used the below queries to fetch the number of columns in a temp table.
select count(*) from information_schema.columns
where table_name = ‘#temptable’
select count(*) Noofcolumns from SYSCOLUMNS
where id=(select id from SYSOBJECTS where name=’#temptable’)
both the above queries didn’t give the desired result.
Please tell me how to retrieve the number of columns in a temp table.
Thanks in advance,
Madan
or
exec sp_columns ‘temporary table’
How to display count and names of Databases in SQL Server
select name from sys.databases
Im having some errors here:
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp
What names should i use? i dont understand this part…
Thanks for reading!
I have two table a where the coulmn is date and userid and another table emp_info contain userid,name dept now I want to find the information from emp_info who is absent in table a between two date where I may assign the holiday.
Please help me
lots of thanks for ur work.. i got my problem solved in short time for finding numbers of rows of each table.