Row count on all tables in the database

Does anyone have a single T-SQL script that could be run against a database that would return the table name and row count for each table?

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +'; ','')+

N'SELECT COUNT(*) AS "'+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N' Count" FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||

Alternate variation:

Code Snippet

declare @.cmd nvarchar(max)

set @.cmd=null

select @.cmd =coalesce(@.cmd +' union all ','')+

N'SELECT '''+

quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)+

N''' AS TableName, COUNT(*) AS "Rows" '+

N' FROM '+quotename(table_catalog)+ N'.'+

quotename(table_schema)+ N'.'+

quotename(table_name)

frominformation_schema.tables

execsp_executesql @.cmd

|||Thanks for the reply Dale. I get results when running this against my master database but not against my DSS database (which is the one I am really after). Is there a variation that would work for my DSS database?|||

The INFORMATION_SCHEMA.TABLES runs against the current database.

Issue USE DSS; in front of the rest of the code.

|||

A fair estimate can be get from:

-- 2000

use your_db

go

dbcc updateusage (0) withcount_rows

go

select

object_name([id]),

rowcnt

from

sysindexes

where

indid in(0, 1)

andobjectproperty([id],'IsUserTable')= 1

andobjectproperty([id],'IsMSShipped')= 0

go

-- 2005

select

object_name([object_id]),

sum([rows])as rowcnt

from

sys.partitions

where

objectproperty([object_id],'IsUserTable')= 1

andobjectproperty([object_id],'IsMSShipped')= 0

groupby

[object_id]

go

AMB

ROUND()

In t-SQL for sql server 2000, the ROUND function has two options
0) normal math rounding (if its less than 5, round down, if its 5 or
greater, round up)
1) round down, reguardless of the number (aka truncate)
Is there any function that will round UP instead of round down? My only
other alternative is to write a UDF, which, in my expierence would kill the
performance of the query.
Thanks,
-- JasonCeiling
select ceiling(12.22)
http://sqlservercode.blogspot.com/|||And it's got a counterpart: FLOOR.
ML
http://milambda.blogspot.com/|||If you care about getting an integer only, use CEILING as suggested by
others. If you care about finer granularities, you can use the following
expression:
ceiling(<value>*power(10, <accuracy> ))/power(10, <accuracy> )
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Jason" <jason@.acd.net> wrote in message
news:ftadnapv46CT5l_eRVn-uA@.giganews.com...
> In t-SQL for sql server 2000, the ROUND function has two options
> 0) normal math rounding (if its less than 5, round down, if its 5 or
> greater, round up)
> 1) round down, reguardless of the number (aka truncate)
> Is there any function that will round UP instead of round down? My only
> other alternative is to write a UDF, which, in my expierence would kill
> the performance of the query.
> Thanks,
> -- Jason
>

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
--
170 --> 170
96.58 --> 97
thanksHere are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round function in sql statement not executing

Hi , I'm new to t-sql so bear with me cos this might be a stupid question.

The code below

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(IIF([OPERATOR]='/',[RATE],1/[RATE]),3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

always gives me the following error message

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

However when i remove the round() code from the statement it executes fine.

Can someone please please let me know what i'm doing wrong here....

Cheers

Hi,

The IIF is for MDX statements. For SQL statements, you need to use the CASE keyword.

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(CASE WHEN [OPERATOR]='/' THEN [RATE] ELSE 1/[RATE] END,3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Here is the definition for the CASE keyword:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Dude,

That was pure ....

Thanks a mil

ROUND function

ROUND(12.5, 0) return 12
How implement in T-SQL (SQL Server 2000) to return 13?SELECT ROUND(12.5, 0)
Returns 13 !
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>|||ROUND(12.5, 0, 1) returns 12
ROUND(12.5, 0, 0) returns 13
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>