Dynamic Stored Proc''s

Question

Post reply

Dynamic Stored Proc''s

Chris Yencer

SSC Rookie

Points: 26
More actions
May 8, 2007 at 10:41 am

#173580

Greetings,
Here is what I'm trying to accomplish. I have a table that holds "stats" on the database. For example, it has the name of the table, number of rows, what the PK is, age of last record, etc...
What I want to do is create an SP that will refresh the stats. Sounds simple, right? Well, trying to get the number of rows, i thought i could write an SP that says
Select @v_NumOfRows = Count(*) from @TableName

And pass the @TableName into the SP. Well it doesn't like that! I also want to use the same approch for getting the Max(Date) etc...

Suggestions?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

DECLARE @sql AS VARCHAR(8000)

SET @sql = 'SELECT ''' + @TableName + ''' As TableName, COUNT(*) AS Total FROM dbo.' + @TableName

INSERT INTO dbo.Stats (TableName, Total)

EXEC (@SQL)

[Edited]

Chris Yencer SSC Rookie Points: 26 More actions · Answer 2

Thank You! It worked with a minor edit. I had to add change 'SELECT ''' + @TableName + ''' AS ...

thank you again!

Chris

tnk SSCrazy Points: 2613 More actions · Answer 3

You could create a view that displays that same information and that would have the added benefit of being real time.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I'm not sure I'm following here. Care to expand a little on your idea?

tnk SSCrazy Points: 2613 More actions · Answer 5

I apologize, I had something in my head that doesn't work: I was thinking you could put the dynamic sql in a user defined function, then make a view that joined sys.tables names column to the UDF call that returned the count on each table. BUT since you can't put dynamic SQL in UDF's, my idea simply does not work!

Dynamic Stored Proc''s

Cookies on SQLServerCentral