Its been quite a while huh? Here's something i've been working on. In trying to get a way from procedural/cursor based TSQL, I've found how to get done what i need using set based programming! In using SQL the way it was meant to be, I can now execute commands faster using set based coding. By declaring a temp table as a variable, I can still use memory-based processing without the overhead of DiskIO. Dynamic SQL is built with a SELECT statement and inserted into the temp table. The reason I used a temp table was the need for a unique identity column to be used as a counter. I have linked servers reaching out to all my database servers so I can remotely run commands or access data. With the example below, I can run a command returning file information for each database on each server! THIS will be extremely handy for building configuration dashboards.
Happy Hunting Data Shepherds!!!
@COUNTER TINYINT = 1,
(COUNTER TINYINT IDENTITY(1,1) NOT NULL,
INSERT INTO @VIEWCOMMAND
'SELECT * FROM [' + S.name + '].[' + D.name + '].SYS.DATABASE_FILES' AS 'COMMAND'
WHILE @COUNTER IS NOT NULL
SELECT @COMMAND =
COUNTER = @COUNTER)
SET @COUNTER =
COUNTER > @COUNTER)