New found love of set based programming

Hey All

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!!!



DECLARE
@COUNTER TINYINT = 1,
@COMMAND NVARCHAR(500)

DECLARE
@VIEWCOMMAND TABLE
(COUNTER TINYINT IDENTITY(1,1) NOT NULL,
COMMAND NVARCHAR(100))

INSERT INTO @VIEWCOMMAND
SELECT
'SELECT * FROM [' + S.name + '].[' + D.name + '].SYS.DATABASE_FILES' AS 'COMMAND'
FROM
sys.servers S,
sys.databases D

WHILE @COUNTER IS NOT NULL

BEGIN

SELECT @COMMAND =
(SELECT
COMMAND
FROM
@VIEWCOMMAND
WHERE
COUNTER = @COUNTER)

EXEC(@COMMAND)

SET @COUNTER =
(SELECT
MIN(COUNTER)
FROM
@VIEWCOMMAND
WHERE
COUNTER > @COUNTER)

END