SQL This!

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

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

E very 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 ...