[MSSQL] - SYNONYM 簡化查詢

 假設

正式機的資料庫為「WORK」

測試機的資料庫為「WORK_TEST」

在撰寫語法的時候，寫的又是

SELECT * FROM WORK.dbo.employee 

這樣在測試機就不能執行

SYNONYM 是 SQL Server 中的一種對象，允許你為另一個資料庫對象（如表、視圖、儲存過程或函數）創建一個別名。其主要作用包括：

1. 簡化查詢：允許你使用簡單的名稱來訪問可能具有長路徑或複雜名稱的對象。
2. 跨資料庫訪問：方便在同一個 SQL 語句中訪問不同資料庫甚至不同伺服器上的對象。
3. 提高靈活性：如果底層對象的位置或名稱改變，只需更新 SYNONYM，而不需要更改所有引用該對象的 SQL 語句。

這時可以再測試區創建 synonym，將 work.dbo.employee 指向 work_test.dbo.employee：

CREATE SYNONYM work.dbo.employee FOR work_test.dbo.employee;

這樣，你可以繼續使用 SELECT * FROM work.dbo.employee，實際上它會查詢 work_test.dbo.employee 表中的數據。

查詢已建立的 SYNONYM

SELECT name AS SynonymName,
       OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       base_object_name AS BaseObjectName
FROM sys.synonyms;


刪除 synonym複製程式碼
DROP SYNONYM RemoteEmployee;

查看已刪除的 SYNONYM

SELECT name AS SynonymName,
       OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       base_object_name AS BaseObjectName
FROM sys.synonyms;

T-SQL - 有效的查詢

create table #table1
(
    c1 varchar(100),
    c2 varchar(100)
)
go
insert into #table1 values('秦始皇','秦')
go
select * from #table1
go

--1. 不要對欄位做運算

select * from #table1 where c1+c2='秦始皇秦' --錯誤

--2. 不要負向查詢

select * from #table1 where c2!='秦X' --錯誤

--3. 不要使用函數

select * from #table1 where substring(c2,1,1)='秦' --錯誤
select * from #table1 where c2 like '秦%' --正確
--drop table #table1

SQL Server - 自動產生編號

create table #t1
(
    salary_month varchar(6),
    emp_no varchar(5),
    salary money
)
go
insert into #t1 (salary_month,emp_no,salary) values('201201','A0001',10000)
insert into #t1 (salary_month,emp_no,salary) values('201202','A0001',15000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0002',30000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0003',20000)
insert into #t1 (salary_month,emp_no,salary) values('201201','A0004',50000)
insert into #t1 (salary_month,emp_no,salary) values('201202','A0004',52000)
insert into #t1 (salary_month,emp_no,salary) values('201203','A0004',51000)
go
select * from #t1
go
--依照每筆排序
select ROW_NUMBER() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go
--依照每筆排序(依照群組跳號)
select RANK() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go
--依照每筆排序(依照群組不跳號)
select DENSE_RANK() OVER(ORDER BY emp_no) AS id,salary_month,emp_no,salary
from #t1

go

--依照每筆排序(依照群組排序)
select ROW_NUMBER() OVER(PARTITION BY emp_no order by salary_month asc ) AS id,salary_month,emp_no,salary
from #t1

MS SQL 判斷是否全部為英數字

--排除空白和單引號之後，去做比對

SELECT * FROM T001 
WHERE PATINDEX('%[^0-9a-zA-Z.,-]%',REPLACE(REPLACE(C001,' ',''),'''',''))=0

SQL Server 查看目前連線數

sp_who 可查看目前連線id

kill 可直接把該連線刪除

快速搜尋整個資料庫中所有表格所有欄位中的所有資料

DECLARE @SearchStr nvarchar(200) = N'使用者本文'


-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT


CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT * FROM #Results

DROP TABLE #Results

SQL Dumper

將資料從SQL Server中撈出，並直接產生insert語法


http://www.ruizata.com/