sp_MSForEachTable -- only current day

How can I use this on ONLY the current day data?  Doing rowcounts, all tables in the given @dbname, but it must be only current day data.

Can't get it into the @wherand without this:

Cannot call methods on nvarchar.

Any advice?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLAuthor Commented:
actually, i suppose information schema view may be more to my gain, simply given the fact that this guy is undocumented.  

i just want a generic proc to run on any database I pass into @dbname, retrieving rowcounts selectively.  by 'selectively', I just mean maybe i into go into the given @dbname and retrieve table counts from all the tables where name like '%Details', and datefield >= today.

stuff like that.  sp_MSForeachTable is fine, or information schema views.  either way....

i've got one together using sp_MSForeachTable, but it's hard-coded to the current database, and I'm just trying to genericize it such that it can be used across any of my instances, for the given @dbname.  the one i've got works fine, but i'm getting all data, not just the current day.

suggestions are welcome
0
dbaSQLAuthor Commented:
suggestions are definitely needed.  :-)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>but it must be only current day data.

please clarify how exactly you understand to apply that rule?
based on a given column name that holds the date of the entry?
or something else?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbaSQLAuthor Commented:
yes, based on a given columnname -- 'tradedate', for example -- i'd want only records inserted current tradedate

i was trying to do it somewhat dynamically -- the @where clause, based on the @dbname, or even a @tablename -- just a very generic proc that would count current day data for the tablenames relevant to the given @dbname

hard-coded, it works just fine.  but to genericize it, i don't know how best to pass that into sp_msforeachtable, or even information schema view/tables.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@dbname will be a problem, because the sp_MSForEachtable runs in the current db ...
so, you just use sp_ prefix for your procedure, and create it in the master db, and it will work the same way...

@tablename
well, you can workaround, saving the value into a table, and use that table data inside the 'query' you run via
exec sp_MsForEachTable to check :


exec sp_MsForEachTable  '
if exists(select null from that_table WHERE table_name = ''?''
 exec( '' select ''''?'''' table_name, count(*) from ? WHERE tradedate >= convert(datetime, convert(varchar(10), getdate(), 120), 120) '' )
'
0
dbaSQLAuthor Commented:
RE @dbname, the below sort of works, angelll.  it's just that i realized after the fact that it's not really generic enough to use in any database on the targeted server.  

Because of this line:  

WHERE EventTime >= GETDATE()", @whereand = "AND o.name LIKE ''%Details'' AND o.name <> ''XXDetails''

'EventTime' is only in 'database1' on the server that I am testing.  As are the o.names LIKE '%Details'.

That's pretty much the reason for this inquiry -- just trying to genericize things enough that I can run it on any database, per server.

The 'tradedate' example I gave before was just an example -- but say I have another database on the server where the date attribute is 'SaleDate', or 'OrderDate', or whatever.... I'd somehow need to dynamically find/use the 'date' field, in order to perform the count on the chosen database.

prolly too much work... too much for what i'm trying to do, that is.  the immediate need is specific to one database, within which I just need to know how much is being written current day.  i think i'll hard code it for that db... unless you've got another suggestion for me.  

:-)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC countproc (
 @dbname varchar(25),
 @where varchar(1000)=NULL,
 @debug bit = 0
)
AS
SET NOCOUNT ON;
/* just a counter -- writes all table counts for the given @dbname into dbadmin.dbo.TableCounts.

exec countproc @dbname = 'database1',@where = 'TableName LIKE ''%Details'' AND TableName <> ''XXDetails''',@debug = 1
exec countproc @dbname = 'database2',@where = 'TableName LIKE ''Index%''',@debug = 1
*/
BEGIN
	DECLARE @sql VARCHAR(4000)
	SET @sql = '
	USE ['+@dbname+'];
	
DELETE dbadmin.dbo.TableCounts
EXEC sp_msforeachtable "INSERT INTO dbadmin.dbo.TableCounts SELECT ''?'' AS TableName,COUNT(*),GETDATE() FROM ? WHERE EventTime >= GETDATE()", @whereand = "AND o.name LIKE ''%Details'' AND o.name <> ''XXDetails'' "

UPDATE dbadmin.dbo.TableCounts SET TableName = replace(tablename,''[dbo].['','''')
UPDATE dbadmin.dbo.TableCounts SET TableName = replace(tablename,'']'','''')

SELECT TableName,RecordCount,InsertTime 
FROM dbadmin.dbo.TableCounts
WHERE '+@where 
	
END

IF (@debug = 1)
  PRINT (@sql)
ELSE
  EXEC (@sql)

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't see the problem, if you KNOW what column names to be put into what server...

you can change:
set @sql = ' ....  XXDetails ... '
into:

set @sql = ' .... ' + @colname + ' ... '
0
dbaSQLAuthor Commented:
that's a good point --say i've got 200 some odd servers -- they all house one or more of the 'standard' databases -- within each, of course, i KNOW what column names to be used for the date attributes, within the given @dbname.

seems i just need to be a little smarter -- if @dbname = 'this', my @colname = 'that'.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>seems i just need to be a little smarter -- if @dbname = 'this', my @colname = 'that'.

be smart, save the information in a config table, and based on the @dbname, query @colname ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
agreed.  thank you for the input, angellll.   (should have thought of that myself)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.