April 12, 2007 at 10:07 am
I have tables in a single server "Server1" which transactions process through, of which only one is actually the active one for the server. I'm trying to write a generic script to identify which table is the active table, since the name may and will vary, and then the next piece is to check that table
if it has a certain quantity of entries. Just a count is really all I need, or something returning a "0" or a "1" for count(*) > X. I've got the following to identify the active table, however I'm clearly going about it the wrong way since I can't make it to the next step. How can I then use my variable name to get me to the actual table? I looked at the table valued function information but I can't quite see how I'd get it put together that way.
My varying tables:
Tran1_Server1
Tran2_Server1
Tran1_server2
The schema for these will be identical. Only one table is actually active for Server1 and will have content flowing through it I want to analyze. That is determined by a TranControl table which looks like:
ServerEntryDirStatus
--------------------------------------
Server11Active
Server12Inactive
Server21Active (for server2 not for server1, but it exists on server1 for replication pieces)
Since the table names can vary, I have the following to identify which is the active table for the server I'm working against, based off of the TranControl table:
CREATE TABLE #ValidateServer
( ServerEntry varchar(32),
DoxDir varchar(2),
ServerName varchar(32))
INSERT #ValidateServer (ServerEntry, Dir,ServerName)
SELECTServerEntry, Dir, @@Servername
FROMTranControl
WHEREStatus = 'ACTIVE'
DECLARE @ActiveTable varchar(64)
SELECT @ActiveTable = (SELECT 'Tran' + Dir + '_' + ServerEntry
FROM #ValidateServer
WHERE ServerEntry = ServerName)
select @ActiveTable
This returns Tran1_Server1.
I want to then be able to say select count(whatever) from Tran1_server1, that's where I'm stuck since it's just a variable.
April 12, 2007 at 11:07 am
-- Option 1
-- Dynamic SQL
DECLARE @SQLString nvarchar(4000)
,@Count int
SET @SQLString = N'SELECT @pCount = COUNT(whatever) FROM ' + @ActiveTable
EXEC sp_executesql @SQLString, N'@pCount int OUTPUT', @Count OUTPUT
SELECT @Count
-- Option 2
-- If the ActiveTable does not change very often, compared to the number of times it needs to be accessed
-- , then use a view to keep the SELECT query static.
-- (The view can be updated with dynamic SQL when the active table changes.)
-- eg:
CREATE VIEW dbo.ActiveTable
AS
SELECT <column_list>
FROM Tran1_Server1
GO
-- then can just use:
SELECT COUNT(whatever)
FROM dbo.ActiveTable
April 13, 2007 at 7:10 am
That did the trick, thanks!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply