DanBrother的部落格

Encrypting and Decrypting a Stored Procedure (T-SQL)
 
-- Create a stored procedure named "sp_test_encryption"
create procedure sp_test_encryption
with encryption
as
 print 'This stored procedure has been encrypted!'
GO

How to Increase the Maximum allowed Attachment File Size for email using sp_send_dbmail
Solution:
[Method 1]
(Within SQL Server Management Studio)
Management -> Database Mail -> Configure Database Mail
-> Next -> View or change system parameters -> Maximum File Size (Bytes) : 1000000 => 5000000

How to login to SQL Server with different OS account?
Solution:
runas /user:domain\new_user "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

e.g.

Query First and Last Datetime of This Month (T-SQL):
DECLARE @now datetime
DECLARE @first_dt datetime
DECLARE @last_dt datetime
DECLARE @first_dt_concat varchar(30)
DECLARE @last_dt_concat varchar(30)

Partitioned Table and Index Creation Example (SQL Server 2005 and up)

USE [testdb]
GO
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2011]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2012]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2013]
ALTER DATABASE testdb ADD FILEGROUP [YEAR_2014]

(1) Restore SQL Server Database With Replace, Recovery Option

use master
go
alter database testdb set offline with rollback immediate

Let SQL Server tell you which indexes need to rebuild
Here's the SQL script:
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 15 THEN 'REBUILD'
ELSE 'REORGANIZE'
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
ELSE ''
END,
avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN
(SELECT object_id ,
index_id ,
avg_fragmentation_in_percent,
partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
) ps
ON t.object_id = ps.object_id
AND ix.index_id = ps.index_id
INNER JOIN
(SELECT object_id,
index_id ,
COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id,
index_id
) pc
ON t.object_id = pc.object_id
AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ix.name IS NOT NULL;

A quick way to run multiple SQL files of MSSQL
Suppose we have multiple SQL files of MSSQL to be executed.
Here's a quick way to do that.

SQL Server - sqlcmd example
usage: Sqlcmd [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, enviroment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]

Database Migration Steps from SQL Server 2000 to SQL Server 2005 or 2008
Source Database [SQL Server 2000]
1. Backup the Database (mydb) to mydb.BAK file