M Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== 查找某些库中含有特定列的所有表并清空 ======
<code sql>
#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';
</code>



====== Update SQL compatibility Level ======
<code sql>
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
</code>


====== Create Sa Login Replacement ======
<code sql 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'
</code>


====== Shrink Log ======
the SQL to shrink log. Run this script to generate SQL script. then run that script.
<code sql 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'
</code>


====== Loop ======
<code sql 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
</code>

====== Cursor Usage ======
<code sql>
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    --释放游标
</code>
====== Update column which includes "Email" ======
<code sql 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
</code>

====== Print "store procedure" ======
<code sql ShowSPContent.sql>
exec sp_helptext N'$StoreProcedureName$'
</code>

====== Add Linked Server ======
<code sql AddLinkedServer.sql>
EXEC master.dbo.sp_addlinkedserver @server = N'$LinkedServerName$', 
@provider=N'SQLNCLI',@datasrc=N'$LinkedServerName$', @srvproduct=N'$LinkedServerName$'
</code>

====== DB Check & Configuration ======
<code sql 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)

</code>