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)
pinaldave
how to find number of columns in a table in sql server 2000.
Hi Vikram,
Above script will do the same for you. Col_Count Variable will give you the number of columns in table.
Regards,
Pinal Dave
(http://www.sqlauthority.com)
HI ,
How can we get Row count for each column of a given table.
where if the row count should say ,
if Nulls are present in entire column for all rows should dispaly – NULL ,
if the entire column is Blank for all rows it should show BLANK.
Thanks.
How would I get column names added along with this query for each table?
hello,
what should we have to write query for counting a number of rows in a table. submit query.
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
how can we find the number of tables starting with some’p'in a database
This works great!
to show the how many records in one database
How can we get the row number of a particular table ?
This SQL Query returns total numbers of column in table
SELECT TOP 100 PERCENT WITH TIES c.TABLE_NAME , COUNT(*) NumColumns
FROM INFORMATION_SCHEMA.[COLUMNS ] c
inner join INFORMATION_SCHEMA.[TABLES] t
ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
and c.TABLE_NAME = T.TABLE_NAME
GROUP BY c.TABLE_NAME
ORDER BY c.TABLE_NAME
Hi Champs ….
Try this
SELECT COUNT(*) Statment will return the row counts !!
and one more thing with using SELECT COUNT(*) statment ….
Because the SELECT COUNT(*) statement makes a full table scan to return the total table’s row count, it can take an extremely long time for large tables. There is another way to determine the total row count in a table. In this case, you can use the sysindexes system table. There is a ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘table_name’) AND indid < 2
Cheers
Praveen
Thanks Pinal you just saved my life :)
Write a Query to select column value in row form
Like
Table
Col1 Col2
Aaa 1
Bbb 2
Ccc 3
Ddd 4
Eee 5
Out put of query should belike
Col1 Aaa Bbb Ccc Ddd Eee
Col2 1 2 3 4 5
hi vinod,
try creating a udf. add a cursor to read each row in your table and concat the values as you read. output the values using the udf a select statement.
hope this helps.
select COUNT(*) from dba_tab_columns
where table_name = YourTableNameHere;
How to get the no. of rows in a table structure ?
hi suganya,
you can write the query in this way
select count(any column name) from yourtable.bcoz u can take any column to find the no of rows
SQL Server 2000 & 2005 both support a stored procedure call sp_help
call it with the tablename following it and you’ll get your table structure and indexes, filegroups, entities, etc….
here are some examples of how to use it…
sp_help MyTableName
sp_msforeachtable “sp_help ‘?’”
sp_msforeachtable “select ‘?’ TableName, count(*) as TotalRecords from ?”
@radhika, @MarownIOM,
Your comments have added value to this blog and community.
Thank you,
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Thanks pinal
hi guys,
how can i show some particular columns from a tabel without writing any column name?means even dnt know abt that particular column name.
bhuvnesh
How can we find out the Column names in SQL Server
like the ORACLE command
DESC
which is use to show the table structure
thanks
—
To find a column name in a table..
Use ”
Go
Select Column_Name from Information_Schema.Columns
where Table_Name = ‘
Note ** you can also use a wildcard
Hopes this helps
Wole
how to list the columns of a particular table when table name is given as input??
Thanks alot for your script. It can be very useful.
Lalit
How to count indexes in particular table. i.e if we have 3 indexes in a particular table then how could we count in query. pls help
hey very nice! thanks Pinal! I am new to SQL and this came in very handy!!
HI Wole,
Thanks for your query finding column names in a tabel using Information_Schema.Columns…
i’m new to sql server…
Thanks & Regds
Rockin Benjamin.S
MS SQL query of showing all tables whose name starts with “P” in specefic database, use this query.
select * from sysobjects where name like ‘P%’
But It will show Constraints and tables,
if you want only user defined tables then use this query.
select * from sysobjects where name like ‘P%’ and xtype = ‘u’
Hi guys,
This is another easy way to find the max row count in a table.
select rowcnt from sysindexes where name in (
select name from sysobjects where parent_obj in
(
select id from sysobjects where name = ‘Table_Name’
)
hi wole
Thanks for helping in counting the number of columns in a table
thanks again
plz anyone send the query for how to count the number of columns in a table
How can we write to query for percentage of the marks?
Hi dave, Thanx for the info. I’m stuck with getting roWID from a select statement. I can’t use ROW_NUMBER() because i’m using 2000 instead or 2005
select count(*) as ‘Total Rows’ from tablename
this query will count number of rows in a table
Find the total rows in Table without the count(*)?
how can i count number of column in a row with same data?
i have a table with column empid,month, day1, day2 ………….day31 i want to count number of ‘p’ and ‘a’ of that employee id?
Column count in a table
create Procedure Prc_GetColumnCount
@TableName varchar(20)
as
Begin
Select Count(Name) from Syscolumns
Where
id =
(Select id from SysObjects Where name = @TableName)
End
Prc_GetColumnCount ‘Table name’
How do we extract all table list from SQL server?
I am using toad for SQL server and trying to find some field.
Can anyone pls tell me how to see the list of all the tables in SQL Server…………………
Hey Awadh ….Here’s ur Answer..
select TABLE_NAME from INFORMATION_SCHEMA.[TABLES]
What is the maximum number of records that SQL will return for a query? e.g. I have a db of 15,000 records and I want to run a query which should return 13,000. Will i see all 13,000 or some fewer number?
Thanks
Hi Friends
You can refer
http://www.mssqlcity.com/Articles/KnowHow/RowCount.htm
SELECT rows FROM sysindexes WHERE id = OBJECT_ID
(‘TableName’) AND indid < 2
Thanks
Hi Frnds
You can get list of all user defined table and no of rows from your sql server database with this query.
It is quiet efficient and optimized.
SELECT Rows, object_Name(id) as TableName FROM sysindexes WHERE indid < 2 and OBJECTPROPERTY(id, ‘IsUserTable’) = 1
Cheers
abhi :)
Hi,
Really this is nice query. Thanks…… i am very happy
Hi,
In T SQL, how can we automate:
- to copy all the columns of a table to make a new table
- change the names of the new table and its columns using something like a prefix, which will make the difference of structures between the two tables.
Thanks
I guess u r looking something for this
Use AdventureWorks;
Select EmployeeID as ‘EmpNo’, LoginId as ‘LoginName’, BirthDate as ‘DOB’
into MyNewTableName
from HumanResources.Employee;
Go;
It creates new table and populates result set in that table
@ Bimpa ,
You can do two things, based on what you are looking at.
1. If you just want a new table structure with out any constraints, keys, indexes…. just the columns and the table thats it, then try using…
select * into new_table_name from old_table_name
This will create the table and at the same time it will copy all data from source table to destination table, if you are using other database for destination table then use the complete notation, like servername.databasename.owner.object name.
you can schedule this tsql as batch…
2. You can try DTS or SSIS package to do the same. It will create a table on the destination, only table no constraints, no indexes … nothing only table with all columns, data types remaining same in both the cases.
This task also creates a table and then inserts all the data from source to destination. you can schedule this package.
3. if you want to have an exact table in the destination with all the features of source table, then you might want to work like this,
create a batch file which will have all these things,
1. script the source table and then run the script on destination, where you want to create the table, this will create a table with the same name with all the features of the source table.
2. if you want to change the name, write another query,
sp_rename object ‘oldname’ , ‘newname’
3. Then final step, insert all the values from source to destination.
insert into new_table_name select * from old_table_name.
and schedule it … I hope it will work… I am not sure !
1. How to find out the table given a column, you only know that there is a table which has this column name, based on this information you can find out what table is it.
use database name
select table_name from information_schema.column where column_name = ‘column name’
2. If you know the table name and you want to know what is the name of the column, there are many ways I am sure, one amoung them is,
sp_help table_name … it will give alot of information… like owner of the table, all column name, their data type, primary keys , foriegn keys, indexes, constraint …. very useful information.
3. You dont know what was the name of the table and what was the name of the column and you still want to search table and column,
use database_name
select table_name , column_name from information_schema.column — copy this code as it is, dont try to change the table_name or column _name
How to find out user defined table names in the database,
use database_name
select * from sysobjects where type = ‘U’
Similarly you can find out other objects created by user, simple change type =
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = In-lined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table ( this is the one I discussed above in the example)
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Hi Pinal,
Please answer my questions?
1. When a database is restored to the original drive with the original filename, is the dbid changed?
2. When a database is restored to a different drive with the original filename, is the dbid changed?
Mani
Hi Pinal,
Here is the scenario:
On Saturday morning, I created a login named abcd using Enterprise Manager (EM).
In the database access tab, I gave abcd login access to databases x, y and z. I gave it db_datareader and db_datawriter roles in all three databases.
When I looked in the databases, I saw abcd user had been created in each database with all the correct privileges.
When I refreshed logins in EM, login abcd did not show that it had access to database z, but still had access to databases x and y.
None of this makes any sense. EM knew the correct database when I created the login and granted access, but did not show it after I refreshed.
When I did the same scenario on Monday morning, it worked properly and EM showed the correct database access.
I want to know what happens?why not in sat but in Monday?
Does SQL Server actually drop the database
when the database is restored?
Regards,
Mani
Each time I google advanced stuff for sql server I keep coming back here ; )
Thanks , thanks , thanks !!!
No one small idea partly made possible by this blog:
Use the description field to pass info for the GUI generator and get rid of all hardcoded names in GUI ; )
SELECT c.name AS [COLUMN_NAME], sc.data_type AS [DATA_TYPE], value AS
[DESCRIPTION]
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE class = 1 and t.name = ‘TableName_tb’
Could somebody tell me how to regex the [DESCRIPTION] = e.g. return only set of this one mathing the regex ?
some of the tables have 0 rows in my database. I want to run a query to list out all the tables with 0 rows. how do i do it?
i tried * from user_tables where num_rows is null
but this query retrieves wrong data.
Can anyone gimme the right query?
Hi
I want to find the perticular number from the column like
ex —11658458784
21565454797
21564567897
now i have to take 6 digit from the all number .
Hi Dave,
please reply back to my questions
a. How to get rows of a column with out the column name.
Thanks in anticipation
bye.
How to get the table structure in mssql similar to “DESCRIBE tablename” in MySQL.
how many tuples we can inserted in a relation
I have got here answer of my question that is “How can i count number of columns in a table”. Thanks Pinal.
HI,
I want to know the table structure using the quiery.
Plz send me the quiery to know abt the table structure
how can i retrive the number of columns in a table is not satisfied for me give me brief introduction
I found an alternative for ‘DESCRIBE tablename’ in mssql
“SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’myTable’;”
I just want to read a specific row from a multi row return table…….i mean suppose……a query return this
date
——-
11/1/2007 3:19:28 PM
12/1/2007 3:19:28 PM
3/1/2008 3:19:28 PM
2/1/2008 3:19:28 PM
now…..how can i get an a specific row from above…..suppose i want to read each row but through numeric no…….if i say row no.3 or row no.2………..what should be the query……if anybody know plz help…….voidbutreturn@yahoo.com
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.
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
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?
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
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?
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
Dear Pinal,
sp_spaceused will never give you updated row count of large table you have to use updateusage before this.
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
Hi Amit
Same you can achieve with PIVOT, you can refer pinal’s very nice artical on this (http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/)
Hope this would help.
Cheers :)
Abhishek Doyle
Mumbai
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…:-)
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
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
Why do these queries on the same table yield different results:
–This statement runs very fast
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(‘mytable’) AND indid < 2
–This one takes a while to execute
select count(*) from mytable
Mytable contains over 3millions records.
I am writing a join. The join is producing more records than desired.
Is there any way I can see the number of columns of the query returned when I say ’select * from…’
Number of columns of the query that was just executed, not of some table that is already there.
Nice Link
Hi All,
How to find the particular row in table?(means suppose one table contain 10 rows , in these 10 rows how to find the particular row(example in 10 rows i want 5 row how)?
hi……
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
its not working in oracle…………
Hi, Ravi you can use following code to get the list of tables having 0 records.
———————————
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
WHERE a.row_count=0
GROUP BY a.table_name, a.row_count, a.data_size
DROP TABLE #temp
—————————-
Hi Dave,
I had a requirement like, I want to show the no of rows(count) as column name in temp table.
Thanks,
Sathish.
Hi Sathish,
if your are using SQL SERVER 2005, then you can use this following syntax
SELECT *,
COUNT(*) OVER(PARTITION BY ””) AS TotalRows
FROM TbaleName
Thanks,
Tejas
Hi
Pinal
Thank you Very Much
Your code to insert multiple rows with one sql statement was useful…
is there any query to get the column count from a table using sql command
Thanks Tejas Shah.
Thanks! This is exactly what I needed!
hi guys,
i don’t usually use sql in my job…. but for this one purpose i’ve to use sqlite…
so can somebody just tell me a simple query to count just the number of columns in a table…
please hurry.. my time is running out…
PS. this sql is not that bad as it seems :)
@Sid
There are many ways to get this done, here are some of these.
select Count(*) ‘No Of columns’ from syscolumns where id = object_id(‘example’)
or
select count(*) ‘No Of columns’ from information_schema.columns where table_name = ‘example’
Or
select Count(*) ‘No Of columns’ from sys.columns where [object_id] = object_id(‘example’)
Hope this helps,
IM.
hi all
thanks for ur comments….
Hi Guys
New to SQL coding so can someone please help? I monitoring our replication and would like to take a row count of a table ‘table1’ and match it to the replication table on our tier 2 and 3 environment .
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
SELECT COUNT(*)AS alais FROM [servername].DB.dbo.Table
But I don’t want to receive and email everytime indication everything is ok. Ideally I would like to receive and email\ alert when the row counts do not match.
The one table I am looking at sits on 5 different servers. Sorry bit stuck !!
Also If I wanted to do row counts for mutiple tables accross the servers i.e table1 on all five servers, table 2, table 3 etc.
How could like be done?
Thanks in advance for your help!!
thx Pinal :) It helps me today to detect that tbXml table in Wsus 3.0 with 160.000 records has 1GB size :OOO
Makers of WSUS are crazy ;)))
Thx anyway for simple and handy snippet.
Regards
Thanks!! great tip saved me a lot of time
Hi Pinal,
Consider there are 5 databases in a sql server.Each database contains n number of tables. A column say ‘EMP id’ is repeated in different tables across the databases.
My requirement is to write a single query which should return the count of that column in all the databases.
Can u help me with this
Thanks !! It’s greate……………
Hi All,
Guys i need to create a reports in SSRs which gives me the comparsion on rowcount of table between 2 different database.
2 different database has the same number of tables and with the same name.
How can i get the rowcount of all the tables in both the database.
Thank in advance
Lalitha
Hi Laitha,
You can write Procedure to get the number of rows from Different Database in same SQL server instance.
For procedure please go : http://www.sqlyoga.com/2009/04/sql-server-query-to-compare-number-rows.html
Thanks,
Tejas
salam
if any one help me in topic how we join the different column in same tabel
How would you do a query that returns the list of all of the column names, data types of each column and max size of each column (ie. varchar(50), varchar(100), etc…)
Been stuck on this with no success ….
Thank you !
Never mind … you already posted this …. of course :)
http://blog.sqlauthority.com/2008/08/05/sql-server-2005-get-field-name-and-type-of-database-table/
SELECT crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate
get_field(crime.fpo,crime.cdd,crime.cdn,crime.cfn,crime.filedate) col
FROM (SELECT DISTINCT fpo,cdd,cdn,cfn,filedate
FROM crime) crime where id=’MARSCARLCR952059′
this query doesnt return the output,,plz correct it
SELECT DISTINCT O.Name, I.Rows
FROM SysObjects O WITH (NOLOCK) JOIN SysIndexes I WITH (NOLOCK) ON (O.ID = I.ID)
WHERE O.xType = ‘U’
AND I.Rows = (SELECT MAX(II.Rows)
FROM SysObjects OO WITH (NOLOCK) JOIN SysIndexes II WITH (NOLOCK) ON (OO.ID = II.ID)
WHERE OO.xType = ‘U’)
Hi,
I’m a novice to sql scripting. Am trying to figure out a design problem, involving some arithmatic computation.
I have two tables temp1 and temp2 with two feilds account no., ( Common in both tables ) balance ( float data type ).
1. I want to compare the balance feilds in temp1 and temp2. print out the no. of accounts and percentage of match and mismatch.
2. output the a/c nos. whose balances dont match into a seperate table..
another question, that I have is, how does sql handle the computation, if a value in a particular field is divided by 0 ?
any help, will get me started.
thanks
Nice Article:
>> Garima Wrote
hi……
select COUNT(*) from information_schema.columns
where table_name = ‘Your_Table_Name
its not working in oracle…………
>> Reply to Garima
I think dba_tab_columns will help you in achieving what you need.
Assuming you are using 10g,
sqlplus / as sysdba
select count(*) from dba_tab_columns where table_name= ‘YOUR_TABLE_NAME’;
For example:
SQL> show user;
USER is “SYS”
SQL> select count(*) from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;
COUNT(*)
———-
8
SQL> select column_name from dba_tab_columns where table_name=’EMP’ and owner=’SCOTT’;
COLUMN_NAME
——————————
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
8 rows selected.
SQL>
Hi,
Can any one help me in writing an SQL Query,
in a database I having number of tables and out of them I want to have count of the tables that has ‘empno’ as one of their column.
Please reply.
Thanks & Regards,
@Rao
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘empno’;
Guys ..
Used the below query …
Sp_spaceused
What if I have tables that are the same name but just different schemas. For instance in the database there was schemaA.TableA and schemaB.TableA. So TableA is listed two times in the same database but under different schemas. The spaceused for that query at the top wont work. Is there another query that will get all the tables and schemas space used?
dear sir pinaldave.
i have one doubt i have created a table dynamically using my .net project and it has not primary key what i did is i have altered the table i add a IndexID using
alter table dbo._COMPANYDETAILS ADD IndexID int;
this query
but now what i need is the table has 5000 records i have to number it in the IndexID column from 1 to 5000 what is the query for it please reply me as soon as possible to my mail(ksnavinkumar@live.com) please sir…
@NavinKumar
ALTER TABLE dbo._COMPANYDETAILS
ADD IndexID int Identity
Thats it. this should do the work.
~ IM.
Hi,
Thanks for such nice query.
It is very nice idea to check entire database.
I find the question from OW very relevant and important. Matching tables by their names duplicate the records from tables with same names in different schemas. Can anybody please shed a light to this point?
Here is my solution using catalog views, which are more flexible than using stored procedures, and considers tables with same name in different schemas:
USE my_DB
SELECT s.[name] [schema]
, t.[name] [table]
, t.create_date
, t.modify_date
, p.rows
, SUM(a.used_pages) * 8 data_KB
FROM sys.schemas s
JOIN sys.tables t
ON s.[schema_id] = t.[schema_id]
JOIN sys.partitions p
ON t.[object_id] = p.[object_id]
JOIN sys.allocation_units a
ON a.container_id =p.partition_id
GROUP BY
s.[name]
, t.[name]
, t.create_date
, t.modify_date
, p.rows
ORDER BY SUM(a.used_pages) DESC
GO
Note that the stored procedure sp_spaceused uses dynamic management view (sys.dm_db_partition_stats) instead of catalog view.