SQL Server query for administration and upgrade. 3 ?s

[MusicInst]

I’m using SQL Server 2000. I need to know if any of my databases are use triggers with “OUTPUT INTO”. 1) How do I find out? 2) How do I find out if “merge” is the name of any table or column in my SQL Server 2000 databases?

3) How do I find out if I’m using XML in my SQL Server 2000 databases? I’m concerned that when I do an upgrade from 2000 to 2008, some of Microsoft’s concerns about XML Showplan and XML semantics validation will be an issue. But the databases I have aren’t overly complex. There may be no XML in them.

 

[markros]

2) Select Table_Name, Column_Name from
Information_Schema.Columns where Column_Name ='Merge' or Table_Name = 'Merge'

PluralSight Learning Library

 

[gmmastros]

3) If you are using XML with SQL 2000, you must also be using the system stored procedure named sp_xml_preparedocument (unless you are string parsing your own XML). To determine if you are using this stored procedure in any of your code...

Select O.Name, O.xtype
From   syscomments As C
       Inner Join sysobjects As O
         On C.Id = O.Id
Where  c.Text Like '%sp_xml_preparedocument%'
Order By O.Name

With the query above, it is possible that you are still using it without the query shown above returning it, but the chances are very slim. You see, syscomments stores the text of the procedure, but if the procedure is longer than 4,000 characters, it splits the procedures in to multiple rows. So... if sp_xml_preparedocument starts anywhere after the 3978th character but before the 4,000th character, the code will be saved with the first part of the function in one row and the 2nd part in another rows. This seems very unlikely to me.

1) Similarly...

Select   O.Name, o.xtype
From     syscomments As C
         Inner Join sysobjects As O
           On C.Id = O.Id
Where    c.Text Like '%output%' and c.text like '%into%'
Order By O.Name

You are likely to get extra rows/procedure names with this one because OUTPUT INTO means one things, but you could have OUTPUT and INTO separately used in a procedure, like select into and using an output variable.

Lastly, take a look at the output of the queries shown above. You'll notice an xtype column in the output. That identifies the type of code. Trigger = TR, Procedure = P, Table Valued Function = TF, etc....

If you see something in XType that you don't recognize, simple do a google search on "SQL Server sysobjects xtype". It should be easy enough to find a complete list of values for this column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom