Script Code of the mobile SQL Server database

Note: This script only requires two parameters, namely, the target database name and target directory.

Example 1

The code is as follows:

Copy code

USE master GO DECLARE @ DBName sysname, @ DestPath varchar (256) DECLARE @ DB table ( Name sysname, Physical_name sysname) BEGIN TRY SELECT @ DBName = 'targetdatabasename ', -- input database name @ DestPath = 'd: SqlData '-- input destination path -- Kill database processes DECLARE @ SPID varchar (20) DECLARE curProcess CURSOR SELECT spid FROM sys. sysprocesses WHERE DB_NAME (dbid) = @ DBName OPEN curProcess Fetch next from curProcess INTO @ SPID WHILE @ FETCH_STATUS = 0 BEGIN EXEC ('Kill '+ @ SPID) Fetch next from curProcess END CLOSE curProcess DEALLOCATE curProcess -- Query physical name INSERT @ DB ( Name, Physical_name) SELECT A. name, A. physical_name FROM sys. master_files Inner join sys. databases B On a. database_id = B. database_id And B. name = @ DBName Where a. type <= 1 -- Set offline EXEC ('alter database' + @ DBName + 'set offline ') -- Move to dest path DECLARE @ Login_name sysname, @ Physical_name sysname, @ Temp_name varchar (256) DECLARE curMove CURSOR SELECT Name, Physical_name FROM @ DB OPEN curMove Fetch next from curMove INTO @ login_name, @ physical_name WHILE @ FETCH_STATUS = 0 BEGIN SET @ temp_name = RIGHT (@ physical_name, CHARINDEX ('', REVERSE (@ physical_name)-1) EXEC ('exec xp_mongoshell ''move "'+ @ physical_name +'" "'+ @ DestPath + '"''') EXEC ('alter database' + @ DBName + 'modify FILE (NAME = '+ @ login_name + ', FILENAME = ''' + @ DestPath + @ temp_name + ''')') Fetch next from curMove INTO @ login_name, @ physical_name END CLOSE curMove DEALLOCATE curMove -- Set online EXEC ('alter database' + @ DBName + 'set online ') -- Show result SELECT A. name, A. physical_name FROM sys. master_files Inner join sys. databases B On a. database_id = B. database_id And B. name = @ DBName END TRY BEGIN CATCH SELECT ERROR_MESSAGE () AS ErrorMessage END CATCH GO

Example 2

Create a SQL Server database code template and a template for creating tables. During development, you can copy it and change it directly.

 

The code is as follows:

Copy code

USE [master] GO If exists (SELECT 1 FROM sysdatabases where name = n' HkTemp ') BEGIN Drop database HkTemp -- if the DATABASE exists, delete the DATABASE first. END GO Create database HkTemp ON PRIMARY -- create a master database file ( NAME = 'hktemp ', FILENAME = 'E: DatabasesHkTemp. Dbf ', SIZE = 5 MB, MaxSize = 20 MB, FileGrowth = 1 MB ) Log on -- create a LOG file ( NAME = 'hktemplog ', FileName = 'E: DatabasesHkTemp. Ldf ', Size = 2 MB, MaxSize = 20 MB, FileGrowth = 1 MB ) GO -- Add a table If not exists (SELECT * FROM sys. objects WHERE object_id = OBJECT_ID (n'hksj _ user') AND type in (n'u ')) BEGIN Create table Hksj_User ( Id int identity (1, 1) not null, SName NVARCHAR (20) not null, SNickName NVARCHAR (20 ), SPassWord NVARCHAR (30) not null, DCreateDate DATETIME, SCreator NVARCHAR (20 ), SEmail NVARCHAR (50 ), SPhone NVARCHAR (50 ), SIdentifyId NVARCHAR (30 ), DLastTimeLogOn DATETIME PRIMARY KEY CLUSTERED ( Id ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO -- Add comments EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' ', @ level0type = n' SCHEMA', @ level0name = n' dbo ', @ level1type = N' TABLE ', @ level1name = n' Hksj _ user', @ level2type = n' COLUMN', @ level2name = n' sname' GO EXEC sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' mailbox ', @ level0type = n' SCHEMA', @ level0name = n' dbo ', @ level1type = N' TABLE ', @ level1name = n' Hksj _ user', @ level2type = n' COLUMN', @ level2name = n' semail'