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)
270 Comments. Leave new
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 :
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 !
exec sp_help 'table_name'
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…
ALTER TABLE dbo._COMPANYDETAILS
ADD IndexID int Identity (1,1)
@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.
How do you get increment rowcount total in a while loop fowhen you are deleting records from a one table and in the end you want to print the total number of records deleted.
@Joel
See if @ROWCOUNT works in your situation.