Critical Status - Database-Oriented Issues Plus

It seems that everything has a critical status, especially when it comes to databases and software development! Here's a place for listing issues and ideas relating to database and development issues. I'll list problems and ideas mainly related to SQL Server 2005. Post your thoughts, advice or complaints! Later this year will be the 25th anniversary of being in the trenches of the IT industry. I specialize in database applications development. I'd like to share my experiences with everyone and learn of those of others. You never stop learning.

Thursday, September 23, 2010

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