Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

March 2008 Blog Posts

Cumulative update package 7 for SQL Server 2005 Service Pack 2

Soon available here http://support.microsoft.com/default.aspx/kb/949095/

posted @ Friday, March 28, 2008 8:14 AM | Feedback (1) | Filed Under [ Administration SQL Server 2005 ]

Some XML search approaches

I just played around with some different techniques to fetch relevant data from XML content.   DECLARE       @XMLString XML,        @Search VARCHAR(50)   SELECT @XMLString = '                            <Customers>                                   <Customer>                                          <FirstName>Kevin</FirstName>                                          <LastName>Goff</LastName>                                          <City type="aca">Camp Hill</City>                                   </Customer>                                   <Customer>                                          <FirstName>Steve</FirstName>                                          <LastName>Goff</LastName>                                          <City type="acb">Philadelphia</City>                                   </Customer>                            </Customers>',               @Search = 'Camp Hill'   -- Get all customers living in Camp Hill SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City/text()[.= sql:variable("@Search")]') = 1   -- Get all customers living in a City containing the text "adel" SELECT cust.coldef.value('FirstName[1]','VARCHAR(20)') AS FirstName,        cust.coldef.value('LastName[1]','VARCHAR(20)') AS LastName,        cust.coldef.value('City[1]','VARCHAR(20)') AS City,        cust.coldef.value('City[1]/@type','VARCHAR(20)') AS Typ FROM   @XMLString.nodes('/Customers/Customer') AS cust(coldef) WHERE cust.coldef.exist('City [contains(.,"adel")]') = 1   -- Get...

posted @ Wednesday, March 26, 2008 11:17 AM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Search all code for specific keyword

This is an updated version for SQL 2005 and later to search all code for a specific keyword SELECT p.RoutineName, 'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec] FROM ( SELECT OBJECT_NAME(so.ID) AS RoutineName, (SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body FROM SYSOBJECTS AS so WHERE so.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X') ) AS p WHERE p.Body LIKE '%YourKeyWordHere%' The types are C = CHECK constraint D = Default or DEFAULT constraint FN = Scalar function IF = In-lined table-function...

posted @ Friday, March 14, 2008 2:05 PM | Feedback (4) | Filed Under [ SQL Server 2008 Algorithms Administration SQL Server 2005 ]

Change schema for all tables

I just helped a guy here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=98346 with schema and thought that someone could benefit from this code exec     sp_MSforeachtable "PRINT '? modify'; ALTER SCHEMA new_schema TRANSFER ?; IF @@ERROR = 0 PRINT '? modified'; PRINT ''"

posted @ Wednesday, March 05, 2008 1:31 AM | Feedback (1) | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]

Powered by:
Powered By Subtext Powered By ASP.NET