Hi guys
I am trying to create a query which counts records for all tables in a
database.
But it seems that I can't use query in From clause as I recieve errors.
Any idea how I can have such a query with variable value in From section?
Select count(*) from ???

Is there something like Dual table in sql that make us able to have queries
which generate queries themselves? If not could you please provide me with a
sample of such query?

Kind Regards
Elham

Elham wrote:
You could retrieve this information from the system tables. Have a look
at the way sp_spaceused <table_name> works and see if you and use some
of its code.

Chris

That depends on how accurate the results need to be.  The source used
by sp_spaceused is not guaranteed to be up to date.  I would be
possible to run DBCC UPDATEUSAGE(0) WITH COUNT_ROWS first though,
which would bring them up to date - for the moment.

Roy Harvey
Beacon Falls, CT

Roy Harvey wrote:
Yes, true. If the OP needs the exact number of rows then he'd have to
build dynamic SQL for each table I guess....

There is an undocumented (treat as unsupported) stored procedure called
sp_MSForeachtable which can be used for this; google it for examples,
but the basic syntax is: exec sp_msforeachtable 'SELECT ''?'' as
TableName, COUNT(*) FROM ?'

Warning: this will take a while.

Stu
Elham wrote:
Show quoteHide quote

Elham wrote:
Run this in the database you want a record count for:

CREATE TABLE #temp (
    TableName VARCHAR(255),
    NumRecs BIGINT
    )

DECLARE @Command VARCHAR(1024)
DECLARE @TableName VARCHAR(255)

SELECT @TableName = ''

SELECT TOP 1 @TableName = name FROM dbo.sysobjects WHERE xtype = 'U'
    AND name > @TableName ORDER BY name

WHILE @@ROWCOUNT > 0
    BEGIN

    SELECT @Command = 'SELECT ''' + @TableName + ''', COUNT(*) FROM ' +
@TableName

    INSERT INTO #temp
    EXEC (@Command)

    SELECT TOP 1 @TableName = name FROM dbo.sysobjects WHERE xtype = 'U'
       AND name > @TableName ORDER BY name

    END

SELECT * FROM #temp

SELECT SUM(NumRecs) AS TotalRecs FROM #temp

sql query - sp
How can I Rearrange my Data
Query taking long-long time
tring’
3 most-recent contact dates