#generate script
SELECT CONCAT('rename table `', TABLE_SCHEMA, '`.', TABLE_NAME, ' to `backup`.bk2022_', TABLE_NAME, ';')
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA IN ('db1', 'db2', 'db3') AND COLUMN_NAME = 'user_code';
 
SELECT CONCAT('create table `', TABLE_SCHEMA, '`.', TABLE_NAME, ' like `backup`.bk2022_', TABLE_NAME, ';')
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA IN ('db1', 'db2', 'db3') AND COLUMN_NAME = 'user_code';
 
#generate ROLLBACK script
SELECT CONCAT('drop table `', TABLE_SCHEMA, '`.', TABLE_NAME, ';')
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA IN ('db1', 'db2', 'db3') AND COLUMN_NAME = 'user_code';
 
SELECT CONCAT('rename table `backup`.bk2022_', TABLE_NAME, ' to `', TABLE_SCHEMA, '`.', TABLE_NAME, ';')
FROM information_schema.`COLUMNS`
WHERE TABLE_SCHEMA IN ('db1', 'db2', 'db3') AND COLUMN_NAME = 'user_code';

SELECT name, compatibility_level FROM sys.databases WHERE name = 'db_name'
 
USE [master]
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'db_name', @new_cmptlevel=90
GO
 
 
 
 
ALTER DATABASE database_name 
SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
 
database_name
 
COMPATIBILITY_LEVEL { 90 | 100 | 110 }
 
要使数据库与之兼容的 SQL Server 版本。 该值必须为下列值之一：
90 = SQL Server 2005
100 = SQL Server 2008 和 SQL Server 2008 R2
110 = SQL Server 2012

create_sa_login_replacement.sql

CREATE PROCEDURE create_sa_login_replacement (@loginname VARCHAR(1000),@execute_immediate bit = 1 )
AS 
 
DECLARE @DATABASES Cursor
DECLARE @DbName AS nvarchar(64)
DECLARE @SQL nvarchar(MAX)
DECLARE @BaseAddUserSql nvarchar(MAX)
DECLARE @BaseAddRoleSql nvarchar(MAX)
DECLARE @BaseAddSchemaSql nvarchar(MAX)
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  VARCHAR (514)
SET @PWD_varbinary = CAST( LOGINPROPERTY( 'sa', 'PasswordHash' ) AS varbinary (256) )
 
DECLARE @charvalue VARCHAR (514)
DECLARE @i INT
DECLARE @LENGTH INT
DECLARE @hexstring CHAR(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @LENGTH = DATALENGTH (@PWD_varbinary)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @LENGTH)
BEGIN
  DECLARE @tempint INT
  DECLARE @firstint INT
  DECLARE @secondint INT
  SELECT @tempint = CONVERT(INT, SUBSTRING(@PWD_varbinary,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
 
SELECT @PWD_string = @charvalue
 
SET @SQL = 'CREATE LOGIN [' + @loginname + '] WITH PASSWORD=' + @PWD_string + ' HASHED , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
IF @execute_immediate = 0 print @SQL ELSE EXEC(@SQL) 
 
EXEC master..sp_addsrvrolemember @loginame = @loginname, @rolename = N'sysadmin'

the SQL to shrink log. Run this script to generate SQL script. then run that script.

ShrinkLog.sql

USE MASTER
 
SET NOCOUNT ON
 
SELECT 'backup log ' + name + ' with truncate_only' + CHAR(13) + CHAR(10) + 'Go'
       + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM   sysdatabases
WHERE  name NOT IN ( 'master', 'tempdb', 'model' )
UNION ALL
SELECT 'use ' + d.name + +CHAR(13) + CHAR(10) + 'dbcc shrinkfile(' + f.name +
       ',1)' + CHAR(13) + CHAR(10) + 'Go' + 
       CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM   MASTER.sys.master_files f
       INNER JOIN sysdatabases d
         ON d.dbid = f.database_id
WHERE  type_desc = 'LOG'

Loop.sql

DECLARE @SQL VARCHAR(400)
DECLARE @i INT
SET @SQL = '2011-10-01'
SET @i=1
while @i<12
BEGIN
 
SET @SQL =  DATEADD(MONTH, 1, @SQL)
 
SELECT COUNT(*) FROM trfp WHERE createDate > @SQL
SET @i=@i+1
END

DECLARE @au_id VARCHAR(11), @au_fname VARCHAR(20)  --申明变量
 
DECLARE authors_cursor CURSOR FOR  --申明一个游标
SELECT au_id, au_fname FROM authors 
--打开游标
OPEN authors_cursor
--取出值
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname
--循环取出游标的值
WHILE @@FETCH_STATUS = 0
BEGIN
  Print @au_id
  Print @au_fname
  Print '###'
  FETCH NEXT FROM authors_cursor 
  INTO @au_id, @au_fname
END
CLOSE authors_cursor         --关闭游标
DEALLOCATE authors_cursor    --释放游标

UpdateEmailSuffix.sql

DECLARE @SQL VARCHAR(400)
DECLARE @oldSuffix VARCHAR(40)
DECLARE @newSuffix VARCHAR(40)
SET @SQL = ''
SET @oldSuffix = '.xiao5.kk'
SET @newSuffix = '.zj'
 
DECLARE sqlCursor cursor FOR
	SELECT updateSql = 
	 'update ' + o.name + ' set ' + c.name + ' = substring(' + c.name + ', 1, len(' 
         + c.name + ') - len(''' + @oldSuffix + ''')) + ''' + @newSuffix + ''' 
         where len(' + c.name + ') > 0 and ' + c.name + ' like ''%' + @oldSuffix + ''''
	FROM sysobjects o
	INNER JOIN syscolumns c ON c.id = o.id
	WHERE c.name LIKE '%email%' AND c.type = 39 AND o.xtype = 'U'
	ORDER BY o.name
 
OPEN sqlCursor
fetch NEXT FROM sqlCursor INTO @SQL
while @@fetch_status = 0
BEGIN
	EXEC(@SQL)
	fetch NEXT FROM sqlCursor INTO @SQL
END
close sqlCursor
deallocate sqlCursor

ShowSPContent.sql

EXEC sp_helptext N'$StoreProcedureName$'

AddLinkedServer.sql

EXEC master.dbo.sp_addlinkedserver @server = N'$LinkedServerName$', 
@provider=N'SQLNCLI',@datasrc=N'$LinkedServerName$', @srvproduct=N'$LinkedServerName$'

DBCheckConfiguration.sql

sp_who
sp_lock
sp_who2
 
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
 
sp_configure 'min server memory', 1024  
RECONFIGURE
GO
 
(MB)