Wednesday, February 16, 2005

How is your Server Configured

The following script works well on Sql Server 2000 (I have not tested it w/ any other versions). The purpose is to gain as much information back on how your sql server is configured (general settings memory etc.)

print 'Free Space'
print '----------'
exec master.dbo.xp_fixeddrives

print 'Memory'
print '------'
exec master.dbo.xp_msver

print 'Database Configuration'
print '----------------------'
exec master.dbo.sp_configure

print'DB Size'
print'-------'
exec master.dbo.sp_databases

print 'Linked Servers'
print '--------------'
exec master.dbo.sp_helplinkedsrvlogin

print 'Windows info'
print '------------'
exec master.dbo.sp_server_info

Monday, February 07, 2005

Join against Lists in a Column

Now how about you want to select against that Table you now get when you push a list of values through it?

this Second part is very usefull tho you will have to edit the table name within when you change source tables... possibly a sproc????

Declare @CursorLess Table (PkID INT, ItemList INT)

--Non Cursor solution
DECLARE @NC Table (PkID INT, ItemList VARCHAR(2000)) --Cursor like holding bin
DECLARE @PkID AS INT, @ListOfItems As VARCHAR(2000) -- IDs pointing back to original table
INSERT INTO @NC (PkID, ListOfItems)
--Change the below select to the actual PKID and List of Items, and Table
Select PkID, ListOfItems From UserTable --Populate Holding bin
----------------------------------------------------
WHILE EXISTS(SELECT * FROM @NC) --Check that data exists
BEGIN
SELECT TOP 1 @PkID = PkID, @ListOfItems = ListOfItems FROM @NC --Set Variables
INSERT INTO @Cursorless (PkID, ListOfItems) SELECT @PkID, Field1 FROM dbo.udfListToTable(@ListOfItems, ';') -- Populate Temp Table
DELETE From @NC where PkID = @PkID --Clear Variables
END

SELECT * FROM @Cursorless -- Use the final Variable table to join approriately....

Sunday, February 06, 2005

List to Table

Every now and again, a developer will be faced with the task of referincing a List of id's. It's typically irresistable to want to push all these IDs into a single field. If you're searching then you may end up considering a cursor... for that I decided to write the following function... it's like other Tsql functions that push a list to a table, but this is my take on it... I release the code here with out warranty.

CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Field1 VARCHAR(6))
AS
BEGIN
--By: Francisco Tapia
--Date: 2/1/2005
--Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql
-- Instead you can join the temp table or use it in your where clause if a field is IN the subquery
DECLARE @FieldText as VarChar(6)

IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
SET @HList = @HList + @Delimiter

WHILE CHARINDEX(@Delimiter, @HList) > 0
BEGIN
IF CHARINDEX(@Delimiter, @HList) > 0
BEGIN
SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
END
ELSE
BEGIN
SELECT @FieldText = RTRIM(LTRIM(@HList))
END
--Insert into Variable Table
INSERT INTO @ListTable(Field1)
SELECT RTRIM(LTRIM(@FieldText))
--Remove Item from list
SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) - CHARINDEX(@Delimiter, @HList))
END
RETURN
END