SQL This!

I found this article while searching for some "text" in my own stored procedures, (the article link is available at the bottom.) --------------- October 6, 2000 Searching Stored Procedures Source Code By Alan Enderby Want to find that stored procedure that deletes those stock items. This simple (& crude) script will scan syscomments for a given string and display the stored procedure and the section of code. if exists (select * from sysobjects where id = object_id('dbo.sp_findsp') and sysstat & 0xf = 4) drop procedure dbo.sp_findsp GO create proc sp_findsp @s varchar(255) as DECLARE @msg varchar(255) ,@ul varchar(255) select @s='%' + @s + '%' select 'SP Name'=upper(o.name), Seq=colid ,'SP Line'=substring(text,patindex(@s,text)-5, 30) from syscomments c , sysobjects o where o.id=c.id and patindex(@s,text) > 0 order by name SELECT @msg='* Stored procedures containing string "' + @s + ...

Data scrubbing is a fact of database life. Whenever you import and normalize data you do run the risk of duplicate data imports as well. This can be especially true if your source database had few restrictions on preventing duplicate data entry. In one particular table I had many rows with duplicate data down to the entrytime field; everything was identical except for the PKID. I tried the typical Cursor script found in various websites, but that would Yield an estimate of 14hrs while the test server processed all 16k rows! I could not very well do that to the production server, on my quest I tried several while loops and eventually came up with this select statement which for my situation (2 duplicate rows for 1 real row of data) First thing I did was dump out all the duplicate rows PK's and FK's into a temp table called #Dup, I hit upon this select statment and I sucessfully narrowed down 16k rows down to 8k of unique data, and was able to delete the duplicate FK's b...

Every once in a while you need a quick Table w/ a range of dates, that's what this is for... Declare @iLoop AS INT SET @iLoop = 0 DECLARE @rptMonths TABLE(rptMonth DateTime) WHILE @iLoop BEGIN INSERT INTO @rptMonths(rptMonth) VALUES(DATEADD(mm, @iLoop, @StartDate)) SET @iLoop = @iLoop + 1 END

This has come up before in my line of work, to get a total workday count but with out the weekends. (DATEDIFF(dd, StartDate, EndDate) + 1) -(DATEDIFF(wk, StartDate, EndDate) * 2) -(CASE WHEN DATENAME(dw, StartDate) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, EndDate) = 'Saturday' THEN 1 ELSE 0 END) AS TotalWorkDays

T his article was written back before I was looking into Sql Server 2005. The underlying idea is the same, in order to keep your database running healthy you will need to maintain and administer the underlying architecture. In this short but subtle refresh I've separated the 2000 concepts and implementations from those used today in Sql Server 2005. If you have comments or corrections do feel free to email me or leave a comment. SQL Server 2000 ===================================================== It is imperative you maintenance your Database. One way to check up on the indexes per table is to run the DBCC SHOWCONTIG command as DBCC SHOWCONTIG ('tbl_YourTableName') with fast,ALL_INDEXES You'll end up with a very similar display like the following... DBCC SHOWCONTIG scanning 'tbl_YourTableName' table... Table: 'tbl_YourTableName' (1113627606); index ID: 1, database ID: 8 TABLE level scan performed. - Pages Scanned................................: 1...

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