SQL DMO in batch / User Function.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL DMO in batch / User Function.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-29 : 11:43:26

Waa writes "Using SQL DMO in batch--OA method to reverse the full database DDL. I pick most of the info from other discussion list. Now I am having problem reversing the DDL for User Define Function in SQL2000. Any help is greatly appreciated.
Here is a snap of the code. See the code below.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if @type = 'U'
-- 0x0004 | 0x0008 | 0x0040 | 0x0080 | 0x2000 | 0x40000 | 0x12008 | 0x1fc00000 = 533012686
select @method = 'Databases("' + @db + '").Tables("' + rtrim(@owner) + '.' + @name +
'").Script(398893260, "' + @scriptfile + '")'

else if @type = 'V'
-- 0x0004 + 0x0040 + 0x40000 = 262212
select @method = 'Databases("' + @db + '").Views("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
else if @type = 'P'
select @method = 'Databases("' + @db + '").StoredProcedures("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
else if @type = 'R'
select @method = 'Databases("' + @db + '").Rules("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
else if @type = 'D'
select @method = 'Databases("' + @db + '").Defaults("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
else if @type = 'TR'

select @method = 'Databases("' + @db + '").Tables("' + rtrim(@tabowner) + '.' + @tabname +
'").Triggers("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'

else if @type = 'UD'
select @method = 'Databases("' + @db + '").UserDefinedDatatypes("' + rtrim(@owner) + '.' + @name +
'").Script(262340, "' + @scriptfile + '")'
else if @type = 'C'
select @method = 'Databases("' + @db + '").CHECK("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
else if @type in ('FN','IF','TF') THEN '.UDF'
select @method = 'Databases("' + @db + '").UserDefinedFunction("' + rtrim(@owner) + '.' + @name +
'").Script(262212, "' + @scriptfile + '")'
print @method"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-29 : 13:35:29

If you are trying to script objcts in the database see
http://www.nigelrivett.net/DMOScriptAllDatabases.html

The s_ScriptObjects is the bit you want and it includes UDF's.

Looking at it your probalem might be that the collection is UserDefinedFunctions not UserDefinedFunction.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources