I am a developer responsible for the database application. I talked about a common distress in the chat with the old colleagues: because I have to generate SQL Scripts almost every day to supply the backup archive or send it to the customer update, so generate SQL Scripts became a daily compulsory course. SQLServer's enterprise manager cannot support the process of saving the generation of SQL Scripts as a setting file, each time you have to do a lot of work, such as: Select an object, set format, table script option, file option. . Old is to repeat such an operation is too much trouble! After the phone is idle, you will think that you can write a small tool, you can provide all the features supporting the SQL Server Enterprise Manager, add a function that supports the settings of each option as a configuration file (such as ini or xm format). ? So I will generate the Scripts format you need, so much better. . . But how to achieve Scripts generation? Consider using SQL-DMO for the previous use! The heart is not as good as action, although the night is deep, but there is a fresh experience to make me spirit. . . Oh, it's not nonsense. .
Just simply talk about how to use SQL-DMO to implement your own needs ~~~~ (Code examples use Pascal language expression) 1. Enumerate all SQLServer instance functions in the LAN: Function Getall ServerInstances: TstringList; Var Oapplication, OSEVERNAMELIST : Variant; i: integer; sNameList: TStringList; begin sNameList: = TStringList.Create; oApplication: = CreateOLEObject ( 'SQLDMO.Application'); oSeverNameList: = oApplication.ListAvailableSQLServers; for i: = 1 to oSeverNameList.Count do sNameList. append (oSeverNameList.Item (i)); Result: = sNameList; oApplication: = NULL; oSeverNameList: = NULL; end; 2. SQLSERVER login to the specified instance function: function LoginServerInstances (sServer, sUser, sPassword: string): Variant Var osqlser: variant; begin osqlser: = creteoleObject ('sqldmo.sqlserver "; OSQLSERV er.LoginTimeout: = 30; oSQLServer.LoginSecure: = False; oSQLServer.AutoReconnect: = True; oSQLServer.Connect (sServer, sUser, sPassword); Result: = oSQLServer; oSQLServer: = NULL; end; two or more functions can help We completed all SQLServer instances in the LAN and log in to a specified instance.
Let's take a look at how to get the table name and stored procedure name of the specified database: 3. Enumerate the name function of all Database, specified SQL Server instance: function getAllDatabaseNameList (OSQLServer: Variant): TstringList; var snamelist: tstringlist; i: integer; Snamelist: = tstringlist.create; for i: = 1 to osqlser.databases.count do snamelist.Append (OSQLServer.Databases.Databases.d; Result: = snamelist; end; 4. Enumeration Specify Database All Table the name of the function: function GetAllTableNameList (oSQLServer: Variant; sDataBaseName: string): TStringList; var oDatabase: Variant; sNameList: TStringList; i: integer; begin sNameList: = TStringList.Create; oDatabase: = CreateOLEObject ( 'SQLDMO.Database') Odatabase: = osqlser.databases.item (sdatabaseename); for i: = 1 to odatabase.tables.count do snam Elist.Append (odatabase.tables.Item (i) .name); Result: = snamelist; odatabase: = null;
The enumeration specifies all StoreProcedure database function name: function GetAllStoreProcedureNameList (oSQLServer: Variant; sDataBaseName: string): TStringList; var oDatabase: Variant; sNameList: TStringList; i: integer; begin sNameList: = TStringList.Create; oDatabase: = CreateOLEObject ( 'SQLDMO.Database'); oDatabase: = oSQLServer.Databases.Item (sDataBaseName); for i: = 1 to oDatabase.StoredProcedures.Count do sNameList.Append (oDatabase.StoredProcedures.Item (i) .Name); Result : = snamelist; odatabase: = null; END; through the above three functions can be done before generating Scripts, and then we can start to see how to generate Scripts. Scripts is also a Method for some objects of SQL-DMO. These objects are as follows: Database, View, StoredProcedure, Trigger, Check, etc. Scripts Method of Syntax is: object.Script ([ScriptType] [, ScriptFilePath] [, Script2Type]) as String ScriptType (integer type) parameter is set to produce what the Scripts, such as its assignment SQLDMOScript_Default, thus creating a It is the normal script of the object, such as StoredProcedure Object, is certainly the content of its Create StoredProcedure. If you assign SQLDMoscript_Drops, then the content of DROP Object is generated. For detailed use of ScriptType, Script2Type (Integer Type) See: C: / Program Files / Microsoft SQL Server / 80 / Tools / Books / SqldMo.chm help files. The return value of the Script method is the text type, whether you fill in the scriptfilepath parameter, you can get the text, that is, Script content.
And the two objects generated by Table, UserDefinedDataType species special, they call Geneatesql Method, whom Syntax is: Object.generateSQL (Database) AS String, relatively simple, no excessive parameters, return value is also Script content. Recommendation: The value of the two parameters of ScriptType, Script2Type can make a constant so that it is more intuitive in the program.
Such as: SqldMoscript_DatabaseperMissions = 32; SqldMoscript_default = 4; .... Based on the above instructions can be established (filling overload when this function is declaring this function): File: // 6. Generate Objects the Sql Scripts file: // (oSqlObjcet: Database, View, StoredProcedure, Trigger, Check) // sScriptFilePath defalut is '' function GetThisObjectScripts (oSqlObjcet: Variant; sScriptFilePath: string; iScriptType, iScript2Type: integer): string; begin Result: = oSqlObjcet.Script (iScriptType, sScriptFilePath, iScript2Type); end; file:. // 7 to generate the specified Objects Sql Scripts // overload Method file: // (oSqlObjcet: Table, UserDefinedDatatype) function TForm1.GetThisObjectScripts (oDatabase, oSqlObjcet: Variant: string; begin result: = osqlobjCet.GenerateSQL (ODatabase); END; When the call is called, the object can be generated according to the interface to write a simple example, take out the MASTER's stored procedure MS_SQLCTRS_USERS Content: Procedure TFORM1.BUTTON4CLICK (Sender: TOBJECT); VAR OSQL Server, ODatabase, OSTOREDPROECEDURE: VARIANT ; ReturnStr: string; begin oDatabase: = CreateOLEObject ( 'SQLDMO.Database'); oSQLServer: = LoginServerInstances ( 'DAVID', 'sa', ''); oDatabase: = oSQLServer.Databases.Item ( 'master'); oStoredProcedure : = OSQL Server.databases.item ('Master'). StoredProcedures.Item ('ms_sqlctrs_users'); file: // Generates Create Procedure Scripts, convert the first 4 to 1 into the ScriptstriPts of Drop Procedure Returnstr: = GetThisObjectscripts (OstoredProcedure, '', 4, 4); showMessTr); END;
