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
SELECT sysobjects.Name [TableName], sysindexes.Rows
FROM sysobjects
JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE type = ‘U’ AND sysindexes.IndId < 2
ORDER BY 1,2
How to get number of columns from select query :
Example:
select xx, yy from trade where ….
number of columns is 2 in this case, is there a way to find out dynamically?? Please help
Why do you want to do this?
To know the number of columns in order to create a matrix. Dont want the user to specify it.
How can we count No of rows in table without using Count (*) or without Count keyword?
Query to find table size for all tables of each database in a server:
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQL VARCHAR(max)
–Main table to keep all data
CREATE TABLE #TempTableMain
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
–table to keep data per database and then truncate for the next database
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
–table to store tablenames in a particular database
create table #tableName (name varchar(100))
–Cursor for running query per database
DECLARE databaseCursor CURSOR
FOR select [name] from msdb.sys.databases
where [name] not in (‘master’,’tempdb’,’model’,’msdb’)
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @DatabaseName
WHILE (@@Fetch_Status >= 0)
BEGIN
–storing tableNames of a particuler database in a temp table
SELECT @SQL = ‘use ‘+@DatabaseName+’
insert into #tableName
select [name] from sys.tables’
exec(@SQL)
–soring table information in temp table
SELECT @SQL =
COALESCE(@SQL + CHAR(13) + ‘ ‘ ,”) +
‘
use ‘+ @DatabaseName+’
INSERT #TempTable
EXEC sp_spaceused ‘+name
from #tableName
print @SQL
exec (@SQL)
–storing data for the current database in the main table
INSERT #TempTableMain
select @DatabaseName,* from #TempTable
–truncating temp tables for next run
truncate table #tableName
truncate table #TempTable
FETCH NEXT FROM databaseCursor INTO @DatabaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
–tables to get size in MB and GB
–this part is optional and configurable as per user and usage
CREATE TABLE #TempTableMainMB
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSizeMB varchar(50),
dataSizeMB varchar(50),
indexSizeMB varchar(50),
unusedSizeMB varchar(50)
)
CREATE TABLE #TempTableMainGB
(
databaseName varchar(100),
tableName varchar(100),
numberofRows varchar(100),
reservedSizeGB varchar(50),
dataSizeGB varchar(50),
indexSizeGB varchar(50),
unusedSizeGB varchar(50)
)
insert #TempTableMainMB
select * from #TempTableMain
–Remove ‘KB’ from tables
update #TempTableMainMB
set reservedSizeMB=REPLACE(reservedSizeMB,’ KB’,”),
dataSizeMB=REPLACE(dataSizeMB,’ KB’,”),
indexSizeMB=REPLACE(indexSizeMB,’ KB’,”),
unusedSizeMB=REPLACE(unusedSizeMB,’ KB’,”)
update #TempTableMainMB
set reservedSizeMB=cast(CAST(reservedSizeMB as INT)/1024 AS varchar(100)),
dataSizeMB=cast(CAST(dataSizeMB as INT)/1024 AS varchar(100)),
indexSizeMB=cast(CAST(indexSizeMB as INT)/1024 AS varchar(100)),
unusedSizeMB=cast(CAST(unusedSizeMB as INT)/1024 AS varchar(100))
insert #TempTableMainGB
select * from #TempTableMainMB
update #TempTableMainGB
set reservedSizeGB=cast(CAST(reservedSizeGB as INT)/1024 AS varchar(100)),
dataSizeGB=cast(CAST(dataSizeGB as INT)/1024 AS varchar(100)),
indexSizeGB=cast(CAST(indexSizeGB as INT)/1024 AS varchar(100)),
unusedSizeGB=cast(CAST(unusedSizeGB as INT)/1024 AS varchar(100))
–display of results
SELECT * FROM #TempTableMain order by 1
SELECT * FROM #TempTableMainMB ORDER BY 1
SELECT * FROM #TempTableMainGB ORDER BY 1
–Final cleanup
DROP TABLE #TempTable
DROP TABLE #TempTableMain
DROP TABLE #TempTableMainMB
DROP TABLE #TempTableMainGB
drop table #tableName
How to Display Table’s column as row
Ex: I Have a Table Employee
Emp_id Emp_Name
101 Santosh
102 Vijay
103 Anilkumar
Now i wanna to display all records of Emp_Name field as horizantally. Like:
Santosh Vijay Anilkumar ………………
Please Help me
Thanks
Santos S.Pawar
You are the best . You save my life every day Thank you..
the no of colunms in table are called?
hi,
how to find the table estimate size based on the number of rows as input. If I give no of rows per a table, it should give estimated size of that table.
No of columns in a table can be returned using the below query:
Select count(*) from user_tab_columns where table_name = ‘tablename’;
Awesomw sir…….
How can we know the maximum number of rows sql server 2005 can hold for any package as oracle.
CREATE FUNCTION FN_GET_AGE_YMD
(
@IP_DOB DATETIME,
@IP_TILLDATE DATETIME
)
RETURNS TABLE
AS
RETURN(
SELECT YEARS,MONTHS,DAYS,DATEDIFF(HOUR,CURDATE,@IP_TILLDATE)-24*DAYS AS HOURS, 0 AS MINUTES, 0 AS SECONDS
FROM(
SELECT *,
CASE WHEN DATEPART(HOUR,CURDATE)>DATEPART(HOUR,@IP_TILLDATE)
THEN DATEDIFF(DAY,CURDATE,@IP_TILLDATE)-1
ELSE DATEDIFF(DAY,CURDATE,@IP_TILLDATE) END DAYS
FROM(
SELECT *,DATEADD(MONTH,MONTHS,CURYEAR)CURDATE
FROM(
SELECT *,CASE WHEN DAY(@IP_DOB)>DAY(@IP_TILLDATE) OR
(DAY(@IP_DOB)=DAY(@IP_TILLDATE)
AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
THEN DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE)-1
ELSE DATEDIFF(MONTH,CURYEAR,@IP_TILLDATE) END MONTHS
FROM(
SELECT *,DATEADD(YEAR,YEARS,@IP_DOB)CURYEAR
FROM(
SELECT CASE WHEN MONTH(@IP_DOB)>MONTH(@IP_TILLDATE) OR
(MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
AND DAY(@IP_DOB)>DAY(@IP_TILLDATE)) OR
(MONTH(@IP_DOB)=MONTH(@IP_TILLDATE)
AND DAY(@IP_DOB)=DAY(@IP_TILLDATE)
AND DATEPART(HOUR,@IP_DOB)>DATEPART(HOUR,@IP_TILLDATE))
THEN DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE)-1
ELSE DATEDIFF(YEAR,@IP_DOB,@IP_TILLDATE) END YEARS
)A)B)C)D)E)
hi all,
Can u pls tell me the query to get all the values from the table .. ie.. for example if i give select * from agent(table name)… it should return me all the values including the column header …it should return me as table to my front end.. the table name will be given by the user.. so there should not be any hardcode of values in the code… I wanna the query to be used in my coding
Hi,
This is also a very good article as usual we got every article here.
As Discussion going here..
my Query is something deeper in this.. I want size of all the tables as group of months..
I want size of all table month wise..
Like I want to know what was the size of my table in january and what was in July so i can compare the increasing rate of my tables..
I want output like that contains month and size of table in that month and table name..
Please Help me…
Hi am amar i am a SQL developer today i got one of the requirement ,Soupose I have 100 tables for each table there is a column mame called ‘status ‘.i want to retrive status column of all the tables.Can any one help me .regarding this query.
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