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
Reference: Pinal Dave (http://www.SQLAuthority.com)
277 Comments. Leave new
How to find no of columns in database table
EXEC sp_tables ‘table name’
great sir…. keep it up
i have 50 rows , i want to display 5-7 records only?
How to write the sql query?
you can use TOP clause.
SELECT TOP 5 * FROM TABLE_NAME
HI,
Is it possible to use aggregate function with (case,when,END) condition use in pivot?
like this (PIVOT (count(Shift_Description) FOR Detail_Date IN (‘ + @cols + ‘)) AS Pvt’)
Below example :-
SELECT
DISTINCT Detail_Date INTO #Dates
FROM
Detail
— WHERE Detail_Date between @StartDate AND @EndDate
ORDER BY Detail_Date
DECLARE @cols NVARCHAR(4000)
SELECT @cols = COALESCE(@cols + ‘,[‘ + CONVERT(varchar, Detail_Date, 106)
+ ‘]’,'[‘ + CONVERT(varchar, Detail_Date, 106) + ‘]’)
FROM #Dates
ORDER BY Detail_Date
DECLARE @qry NVARCHAR(4000)
SET @qry =
‘SELECT Fname AS F,’ + @cols + ‘ FROM
(SELECT Fname, Detail_Date,Shift_Description
FROM Detail)p
PIVOT (count(Shift_Description) FOR Detail_Date IN (‘ + @cols + ‘)) AS Pvt’
print +@qry
EXEC(@qry)
f possible, can you please suggestion how to use it?
HI I AM DIPAK
INPUT
Id Name
1 A
2 A
3 B
4 C
5 D
OUTPUT
NAME COUNT
A 2
B 1
C 1
D 1
I WANT TO KNOW SQL QUERY FOR THE ABOVE TABLE
Hi dave
Is there any way to Find the Space of All table by filtering the Row.
Like I have Foreign Key ‘Company ID ‘ In All Table would it be possible if i want to know the space Occupied by Company
Thanks
Junaid
write an SQL query that returns the sum of the numbers in column v.
For example, given:
v
—
2
10
20
10
your query should return 42.
Code please
How can I get size of a column in a table ? I mean if I want to know, size of some particular column is there any command like sp_spaceused ? I believe sp_Spaceused give size of only table and not particular Column.
SELECT * FROM TB_NAME
ORDER BY COLUMN NAME
OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY