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
hi dave
how to count the no of columns in a table…
If I have a column whose every individual row value is known,
how (by mssql query) can I calculate the row number of one particular value in that column?
That is , what is row number of a value in a column?
eg, a column ‘animal’ consists of 5 row values. cat,dog,man,bird,fly.
the value ‘man’ belongs to 3rd row. But if I know only the value ‘man’ how can I calculate the corresponding row number , which is 3? (only by MSSQL query,using access 2003, and visual studio 2005)
Hi,
This is mustafa.
I wrote one stored procedure.
Inside the stored procedure I wrote one query it return mulitple set of record I want add all values that mean total what function I have use.
Is this?
select sum(col) from table
I need row count for all the tables in a database that utilizes schemas.
Hi,
Please bear with me as i am a newbie
Is there any way to find out the no of rows for each month in a year for the last 10 years. so i should effectively get 120 rows (12months X10 years). I am able to create a stored procedure to retrive data based on month and year but cant i get this information at one shot?
This is what i could come up with
create procedure usp_emp_birth @month varchar(10),@year varchar(10)
as
(
select @month as month ,@year as year, count(*)as [no of rows] from HumanResources.Employee
where datename(month,birthdate)= @month and datepart(year,birthdate)= @year
)
exec usp_emp_birth may,1970
how to find number and different tables in perticular schema
select table_name from information_schema.tables
where table_schema='your_schema'
my result set is:
Name data
abc new
sss old
abc current
xxx jadu
Now i requered show on follwing formate:
abc new
sss old
current
xxx jadu
pls give me solution?
It is called Suppress if duplicated
If you use front end application, do it there
How to get table list(table names) for a database according to batch size.
e.g. if i there r 50 tables in database then if i want table names from 10 to 30 range.
reqired in sql 2000 and oracle
Search for Pagination in google/bing
Hi, Dear
Thank for co-operation to me
by your website
given the number of rows and columns how do we find the degree of Table?
For example: What’s the degree of table having 10 cols and 1000 rows
How to find ,how many columns in a table?
select count(*) from information_schema.columns
where table_name='your_table'
Its really good site
Hi,
I have a table like:
Table1
(
col1,
col2,
col3,
)
It has following sample data:
col1 col2 col3
1 2 3
I want to run a query which provides the following output:
COL1 1
COL2 2
COL3 3
Can you please show me how to do this. I am using SQL Server 2005.
Thanks,
Amit
select 'col1' as col, col1 as value from table
union all
select 'col2' as col, col2 as value from table
union all
select 'col3' as col, col3 as value from table
Dear Pinal,
sp_spaceused will never give you updated row count of large table you have to use updateusage before this.
From version 2005 onwards, it is automatically updated and no need to use updateusage
Hi,
Nice info, but i have started learning SQL now. Could you pls give some tips on how to go about this. I am a tester with 4+years of experience.
Thanks
Sachin
Good.
you can switch to oracle.
Hi all,
How to print the column name and that values in a row? I need to use that in one select query.
(i.e)
Dep.Code No.Of.Staff
——————————–
101 25
103 40
Total 65
Thanks,
Karthik…:-)
select dept_code, count(*) as no_of_staff from your_table
group by dept_code
Hi all,
I have a stored procedure which is used for insert/update of records in a table. A log is maintained for each operation performed by the user, in the application, and i need to insert the record into the log table only if successful transaction of insert/update is committed.
For the log table, i need the column name of the row which is been updated in the query executed above.
Please tell me how to get the column name which is been updated by the “Update” statement?
Thanks in advance.
Great job. Exactly what I was looking for.
To find out no of columns in a table
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
how to find number of columns in a table without using count(*) ?
Reply soon.
Vanita
Hi,
How to return column names where the column contains a particular value. This will always be a single row – each column will contain either 1 or 0. I want to only return the columns that contain the 0 value.
many thanks