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
