/ *?
?????????? Call SQLDMO to generate scripts - functions in the query analyzer?
?
/ * - Call the instance?
?????????? print? dbo.fgetscript ('zj', '', '', 'xzkh_sa', 'syscolumn')?
?
/ * - get the script of all objects?
DECLARE? @name? varchar (250)?
DECLARE? #aa? cursor? for?
?????????? SESOBJECTS? WHERE? Xtype? NOT? IN ('s', 'pk', 'd', 'x', 'L')?
Open? #aa?
Fetch? next? from? #aa? INTO? @name?
While? @@ fetch_status = 0?
Begin?
?????????? print? dbo.fgetscript ('zj', '', '', 'xzkh_sa', @ name)?
?????????? fetch? next? from? #aa? INTO? @name?
END?
CLOSE? #aa?
DEALLOCATE? #aa?
- * /?
* /?
PRINT? DBO.FGETScript ('gisweb', 'sa', ',' hf ',' sct_sb_dyhgq ')
IF? EXISTS (SELECT? 1? from? sysobjects? Where? id = Object_id ('fgetscript')? And? ObjectProperty (id, 'isinlineFunction') = 0)?
?????????? Drop? Function? FGETScript?
Go?
?
Create? Function? FGETScript (?
?????????? @servername? VARCHAR (50) ???????????????????????????????????????????????? ?????????????????????????? --server name?
???????????, @ userid? varchar (50) = 'sa' ??????????????????? ????????????????????-user name, if it is a NT authentication method, is it empty?
???????????, @password? varchar (50) = '' ???????????????????????????????????????? ???????????????????-password?
???????????, @ databaseename? varchar (50) ???????????????????????????????????????????? ??????????????? --Name database?
???????????, @ ObjectName? varchar (250) ???????????????????????????????????????????????? ???????????????-object name?
?
RETURNS? VARCHAR (8000)?
AS?
Begin?
?????????? declare? @re? varchar (8000) ??????????????????????? ??????????????????????????????????????????? ??????????????-return script?
?????????? declare? @SRVID? INT, @ dbsid? int ????????????????????? ??????????????????????????????????????????? ???? - Define the server, database set ID ??????????? declare? @Dbid? Int, @ TBID? INT ???????????????? ??????????????????????????????????????????? ??????????????????????????????-database, table ID?
?????????? Declare? @err? int, @ src? varchar (255),? @Desc? varchar (255) ??????????? - Error handling variable?
?
- Create a SQLDMO object?
?????????? EXEC? @ err = sp_oacreate? 'sqldmo.sqlserver', @ srvid? Output?
?????????? f @err <> 0? goto? lberr?
?
--connect to the server?
?????????? if? isnull (@USERID, '') = ''? - if it is? NT authentication mode?
?????????? begin?
??????????????????????????????? EXEC? @ err = sp_oasetproperty? @ srvid, 'loginsecure', 1?
??????????????????????? f @err <> 0? goto? lberr?
?
??????????????????????? EXEC? @ err = sp_oamethod? @ srvid, 'connect', null, @ servername?
?????????? End?
?????????? ELSE?
???????????????????????????????? EXEC? @ err = sp_oamethod? @ srvid, 'connect', null, @ servername, @ userid, @ password?
?
?????????? f @err <> 0? goto? lberr?
?
- Get database sets?
?????????? EXEC? @ err = sp_oAgetProperty? @ SRVID, 'DATABASES', @ dbsid? Output?
?????????? f @err <> 0? goto? lberr?
?
- Get the database ID to get the script?
?????????? EXEC? @ Err = sp_oAmethod? @ dbsid, 'item', @ dbid? Output, @ databaseename?
?????????? f @err <> 0? goto? lberr?
?
- Get the object ID to get the script?
?????????? exec? @ err = sp_oAMethod? @ dbid, 'getObjectByname', @ td? Output, @ Objectname?
?????????? f @err <> 0? goto? lberr?
?
- get a script?
?????????? EXEC? @ = sp_oamethod? @ TBID, 'script', @ RE? OUTPUT ??????????? IF? @err <> 0? goto? lberr ?
?
?????????? -Print? @RE?
?????????? Return (@RE)?
?
lberr:?
?????????? EXEC? sp_oageterrorinfo? null ,? @src? out ,? @Desc? OUT ???
?????????? Declare? @errb? varbinary (4)?
?????????? set? @ errb = CAST (@err? as? varbinary (4))?
?????????? EXEC? MASTER..XP_VARBINTOHEXSTR? @ Errb, @ re? OUT?
?????????? set? @ RE = 'error number:?
' @ RE?
???????????????????????????????????? char (13) 'error source:?
' @ src?
???????????????????????????????????? char (13) 'error description:?
' @ DESC?
?????????? Return (@RE)?
END?
Go
