Use adoquery to create an SQL Server database and Create Table structures, stored procedures, and views.

I installed the system yesterday and made it a big deal. Now I want to write the summary to my chin.

PS: attachCodeThe indentation is out of order.

The SQL files exported using the SQL Server Enterprise Manager cannot be directly used in adoquery. Because adoquery does not support go and does not support annotations, the creation of views and stored procedures must start with the query, therefore, the SQL file exported by the enterprise manager must be processed before it can be used.

First, export the SQL statement

Set

 

 

 

Pay attention to this step. Do not forget to select "Write primary key, foreign key, default value, and check constraint script. The file format should be ANSI, because it seems that tstrings. loadfromfile does not support Unicode text. If your code does not use tstringlist to read SQL files, it doesn't matter what to choose here. If you select to create a file, you can ensure that the structure is created in order to avoid dependency between multiple files. It is troublesome to analyze which file must be created first.

Then, create a database

We strongly recommend that you set the paramcheck attribute of adoquery used to create databases and database structures to false.

(If you have to set it to true, an error will be reported in the future, but don't blame me for not blocking you)

Delete the part of the database created in the header in the generated SQL file (that is, until the first use XXX clause, including this clause, is also deleted ), we need to assemble the statements for creating a database.

To create a database, you need to know the location where the database files and logs are stored (forcing users to specify the initial size of the database and log files that appear to be rude, preferably automatically obtained), Database names and sorting rules.

How to store database files and logs: (I use the location of the first database file as the default storage location)

Adoquery master database: Select top 1 filename from sysaltfiles

The default storage location is includetrailingpathdelimiter (extractfilepath (TRIM (fieldbyname ('filename'). asstring )))

Create a database (still executed in the master database ):

SQL. Add (format ('If exists (Select name from Master. DBO. sysdatabases where name = n' % s') ', [tedbname. Text]);
SQL. Add (format ('drop database [% s] ', [tedbname. Text]);
SQL. Add (format (
'Create database [% s] On (name = n' % s_data '', filename = n' % S % s_data.mdf'', size = 7, filegrowth = 10% %) log On (name = n' % s_log'', filename = n' % S % s_log.ldf'', size = 7, filegrowth = 10% %) Collate chinese_prc_ci_as ',
[Tedbname. Text, tedbname. Text, fdbpath, tedbname. Text, tedbname. Text, fdbpath, tedbname. Text]);
Execsql;

The two sizes are the initial sizes of database files and log files, in MB. Chinese_prc_ci_as is the default sorting rule

Note that if the database is being used, the drop operation will fail. It seems that sqldmo can be used to kill the process that connects to the database. You can use this brutal method to stop using the database...

Modify SQL statements and create database structures

Run the following command in the created database:

1. Remove comments (-- And /**/)

MARK: = false;
For index: = 0 to sqllist. Count-1 do
Begin
// -- Start
Tmpstr: = trim (sqllist [Index]);
If copy (tmpstr, 1, 2) = '-- 'then
Sqllist [Index]: =''
Else begin
If Mark then
Begin
If copy (tmpstr, length (tmpstr)-1, 2) = '*/' then
Begin
MARK: = false;
End;
Sqllist [Index]: = '';
End
Else begin
If copy (tmpstr, 1, 2) = '/*' then
Begin
MARK: = true;
Sqllist [Index]: = '';
End;
End;
End;
End;

2. Remove go

Sqllist. Text: = stringreplace (sqllist. Text, #13 #10 'Go' #13 #10, #13 #10, [rfreplaceall, rfignorecase]);

3. Add exec before create View

Sqllist. Text: = stringreplace (sqllist. Text, 'create view', 'exec ('create view', [rfreplaceall, rfignorecase]);
MARK: = false;
For index: = 0 to sqllist. Count-1 do
Begin
If Mark then
Begin
// Replace 'in Exec ('') with''. Otherwise, an error is returned.
Sqllist [Index]: = stringreplace (sqllist [Index], ''', ''', [rfreplaceall, rfignorecase]);
If system. pos ('set', sqllist [Index])> 0 then
Begin
MARK: = false;
// Add 'At the end of create view ')
Sqllist [index-1]: = sqllist [index-1] + ''')';
End;
End
Else if system. pos ('exec (''create view', sqllist [Index])> 0 then
Begin
MARK: = true;
End;
End;

4. Add exec before the Stored Procedure

Note that set appears frequently in the stored procedure. Therefore, you cannot use set as a marker for judging the end of 'create procedure.

Set quoted_identifier off, as long as it is the statement for creating a stored procedure exported by the Enterprise Manager, it must follow the creation statement with set quoted_identifier off

Sqllist. Text: = stringreplace (sqllist. Text, 'create procedure ', 'exec ('create procedure', [rfreplaceall, rfignorecase]);
MARK: = false;
For index: = 0 to sqllist. Count-1 do
Begin
If Mark then
Begin
// Replace 'in Exec ('') with''. Otherwise, an error is returned.
Sqllist [Index]: = stringreplace (sqllist [Index], ''', ''', [rfreplaceall, rfignorecase]);
If system. pos ('set quoted_identifier off', sqllist [Index])> 0 then
Begin
MARK: = false;
// Add 'At the end of create procedure ')
Sqllist [index-1]: = sqllist [index-1] + ''')';
End;
End
Else if system. pos ('exec (''create procedure ', sqllist [Index])> 0 then
Begin
MARK: = true;
End;
End;

Last

SQL. Assign (sqllist), you can execute the query

 

However, I think sqldmo should have the function of directly executing SQL statements, that is, it supports the transact-SQL and time relationships such as go and annotations, and does not go into in-depth study of sqldmo, for the moment, I just used this game to refresh the Database List.