Soon available here
http://support.microsoft.com/default.aspx/kb/949095/
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...
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...
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 ''"