CREATE DATABASE (SQL Server Transact-SQL)

https://msdn.microsoft.com/zh-tw/library/ms176061(v=sql.120).aspx

建立TRUNCATE TABLE的Stored procedure

--建立Stored procedure
CREATE PROCEDURE [dbo].[mysp_truncate]  @tableName varchar(50)
with execute as owner
AS
DECLARE @SQL VARCHAR(2000)
SET @SQL='TRUNCATE TABLE dbo.' + @tableName
EXEC (@SQL);
GO
--授予執行權限
--這樣就算使用者沒有ALTER權限也可以TRUNCATE TABLE
--ldf檔才不會一直增加(寫入transaction log)
grant execute on [dbo].[mysp_truncate] to 資料庫使用者;
GO
--執行Stored procedure
exec mysp_truncate 表格名稱;

出處:
Creating a Stored Procedure to Truncate a Table


TRUNCATE TABLE (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms177570.aspx
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.


ref:
EXECUTE AS 子句 (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms188354(v=sql.120).aspx

sqlcmd 執行sql檔

sqlcmd -S 資料庫位址 -d 資料庫名稱 -i CUSTOMERS.sql > log\CUSTOMERS.log

ref:
https://msdn.microsoft.com/zh-tw/library/ms180944(v=sql.120).aspx
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017

列出SQL Server資料庫所有Views

select * from sys.tables where type = 'V'

列出SQL Server資料庫所有Tables

select * from sys.tables where type = 'U'

Select count(*) from multiple tables

http://stackoverflow.com/questions/606234/select-count-from-multiple-tables

 

橫的

with t1_count as (select count(*) c1 from t1),
     t2_count as (select count(*) c2 from t2)
select c1,
       c2
from   t1_count,
       t2_count
/

select c1,
       c2
from   (select count(*) c1 from t1) t1_count,
       (select count(*) c2 from t2) t2_count
/

直的

SELECT 'TAB1' AS TABLE_NAME, COUNT(*) FROM TAB1
 UNION ALL SELECT 'TAB2' AS TABLE_NAME, COUNT(*) FROM TAB2
 UNION ALL SELECT 'TAB3' AS TABLE_NAME, COUNT(*) FROM TAB3
 UNION ALL SELECT 'TAB4' AS TABLE_NAME, COUNT(*) FROM TAB4

Chocolatey: package manager for Windows

https://chocolatey.org

Turbo Services Manager

http://www.turboirc.com/tsm/

資料庫比對軟體Open DBDiff

http://opendbiff.codeplex.com/Wikipage?ProjectName=opendbiff

Toolwiz Time Freeze系統還原軟體

http://www.techbang.com/posts/19270-infinite-regeneration-windows-81-boot-restore-cheat-secretly-reported-74-special-feature

由於Time Freeze 2014尚未支援保護非系統磁碟機，因而我們僅需自動排除「C:」槽中的目錄

官網
http://www.toolwiz.com/products/toolwiz-time-freeze/

方便管理的Windows工作列軟體：Bins

http://www.gadgetoflife.com/?p=5254

http://www.1upindustries.com/bins/