Category Archives: SQL Server

將 object owner 非 dbo 的改成 dbo

declare cur cursor for
SELECT ‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + [name] + ‘]’
FROM sys.objects
WHERE USER_NAME(OBJECTPROPERTY(object_id, ‘OwnerId’)) not in(‘dbo’,’sys’) and is_ms_shipped=0
declare @obj nvarchar(1000),@sql nvarchar(1000)
open cur
fetch cur into @obj
while @@FETCH_STATUS=0
begin
    set @sql=’alter authorization on ‘ + @obj  + ‘ to dbo’
    –print @sql
    exec(@sql)
    fetch cur into @obj
end
close cur
deallocate cur

廣告

rename 預存程序後,最好再 alter 一遍

聽課的朋友提出的有趣問題,sp rename 後,查詢 sp_helptext 的 create proc 部分卻使用舊的預存程序名稱。實作範例如下:

 

use tempdb
go
create proc sp_a
as
print ‘hello’
go
exec sp_rename ‘sp_b’,’sp_b’

–rename 後保有語法定義 metadata 的內容不會變,所以會回傳如下的警告

Caution: Changing any part of an object name could break scripts and stored procedures.

select object_name(id) objectame,* from syscomments
select object_name(object_id) objectname,* from sys.sql_modules

image

 

–所以取回錯誤的結果

sp_helptext ‘sp_b’
go

image

 

–可以再建同名的 sp

create proc sp_a
as
print ‘hello a’

exec sp_helptext ‘sp_a’
exec sp_helptext ‘sp_b’
go

 

–有趣的是利用 object explorer 會對

image

–但透過 profiler 到的語法如下

exec sp_executesql N’SELECT
NULL AS [Text],
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(sp.name=@_msparam_3 and SCHEMA_NAME(sp.schema_id)=@_msparam_4)’,N’@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)’,@_msparam_0=N’P’,@_msparam_1=N’RF’,@_msparam_2=N’PC’,@_msparam_3=N’sp_c’,@_msparam_4=N’dbo’

查詢的依然是 sys.sql_modules,所以仍是錯的,顯然 object explorer 會自行依物件名字改正產出的語法

–alter 之後會改成正確的 create proc sp_b

alter proc sp_b
as
print ‘hello’

所以 rename 之後,最好再 alter 一遍,保證 metadata 的正確性

透過 T-SQL Script 建立 Table 定義

Sorry,忘了來源出處,但為了讓朋友 Copy,所以貼在此處…

CREATE proc [ETL].[spCreateTable] @table_name SYSNAME,@On nvarchar(max)=",@SQLScript nvarchar(max) output
as
DECLARE
      @object_name SYSNAME
    , @object_id INT

SELECT
      @object_name = ‘[‘ + s.name + ‘].[‘ + o.name + ‘]’
    , @object_id = o.[object_id]
FROM CPADW.sys.objects o WITH (NOWAIT)
JOIN CPADW.sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
WHERE s.name + ‘.’ + o.name = @table_name
    AND o.[type] = ‘U’
    AND o.is_ms_shipped = 0

DECLARE @SQL NVARCHAR(MAX) = “

;WITH index_column AS
(
    SELECT
          ic.[object_id]
        , ic.index_id
        , ic.is_descending_key
        , ic.is_included_column
        , c.name
    FROM CPADW.sys.index_columns ic WITH (NOWAIT)
    JOIN CPADW.sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id
    WHERE ic.[object_id] = @object_id
),
fk_columns AS
(
     SELECT
          k.constraint_object_id
        , cname = c.name
        , rcname = rc.name
    FROM CPADW.sys.foreign_key_columns k WITH (NOWAIT)
    JOIN CPADW.sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id
    JOIN CPADW.sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id
    WHERE k.parent_object_id = @object_id
)
SELECT @SQL = ‘CREATE TABLE ‘ + @object_name + CHAR(13) + ‘(‘ + CHAR(13) + STUFF((
    SELECT CHAR(9) + ‘, [‘ + c.name + ‘] ‘ +
        CASE WHEN c.is_computed = 1
            THEN ‘AS ‘ + cc.[definition]
            ELSE UPPER(tp.name) +
                CASE WHEN tp.name IN (‘varchar’, ‘char’, ‘varbinary’, ‘binary’, ‘text’)
                       THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length AS VARCHAR(5)) END + ‘)’
                     WHEN tp.name IN (‘nvarchar’, ‘nchar’, ‘ntext’)
                       THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘MAX’ ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ‘)’
                     WHEN tp.name IN (‘datetime2’, ‘time2’, ‘datetimeoffset’)
                       THEN ‘(‘ + CAST(c.scale AS VARCHAR(5)) + ‘)’
                     WHEN tp.name = ‘decimal’
                       THEN ‘(‘ + CAST(c.[precision] AS VARCHAR(5)) + ‘,’ + CAST(c.scale AS VARCHAR(5)) + ‘)’
                    ELSE "
                END +
                CASE WHEN c.collation_name IS NOT NULL THEN ‘ COLLATE ‘ + c.collation_name ELSE " END +
                CASE WHEN c.is_nullable = 1 THEN ‘ NULL’ ELSE ‘ NOT NULL’ END +
                CASE WHEN dc.[definition] IS NOT NULL THEN ‘ DEFAULT’ + dc.[definition] ELSE " END +
                CASE WHEN ic.is_identity = 1 THEN ‘ IDENTITY(‘ + CAST(ISNULL(ic.seed_value, ‘0’) AS CHAR(1)) + ‘,’ + CAST(ISNULL(ic.increment_value, ‘1’) AS CHAR(1)) + ‘)’ ELSE " END
        END + CHAR(13)
    FROM CPADW.sys.columns c WITH (NOWAIT)
    JOIN CPADW.sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    LEFT JOIN CPADW.sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN CPADW.sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN CPADW.sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE c.[object_id] = @object_id
    ORDER BY c.column_id
    FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, CHAR(9) + ‘ ‘)
    + ISNULL((SELECT CHAR(9) + ‘, CONSTRAINT [‘ + k.name + ‘] PRIMARY KEY (‘ +
                    (SELECT STUFF((
                         SELECT ‘, [‘ + c.name + ‘] ‘ + CASE WHEN ic.is_descending_key = 1 THEN ‘DESC’ ELSE ‘ASC’ END
                         FROM CPADW.sys.index_columns ic WITH (NOWAIT)
                         JOIN CPADW.sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
                         WHERE ic.is_included_column = 0
                             AND ic.[object_id] = k.parent_object_id
                             AND ic.index_id = k.unique_index_id    
                         FOR XML PATH(N"), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “))
            + ‘)’ + CHAR(13)
            FROM CPADW.sys.key_constraints k WITH (NOWAIT)
            WHERE k.parent_object_id = @object_id
                AND k.[type] = ‘PK’), “) + ‘) ‘ + @On + CHAR(13)
    + ISNULL((SELECT (
        SELECT CHAR(13) +
             ‘ALTER TABLE ‘ + @object_name + ‘ WITH’
            + CASE WHEN fk.is_not_trusted = 1
                THEN ‘ NOCHECK’
                ELSE ‘ CHECK’
              END +
              ‘ ADD CONSTRAINT [‘ + fk.name  + ‘] FOREIGN KEY(‘
              + STUFF((
                SELECT ‘, [‘ + k.cname + ‘]’
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “)
               + ‘)’ +
              ‘ REFERENCES [‘ + SCHEMA_NAME(ro.[schema_id]) + ‘].[‘ + ro.name + ‘] (‘
              + STUFF((
                SELECT ‘, [‘ + k.rcname + ‘]’
                FROM fk_columns k
                WHERE k.constraint_object_id = fk.[object_id]
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “)
               + ‘)’
            + CASE
                WHEN fk.delete_referential_action = 1 THEN ‘ ON DELETE CASCADE’
                WHEN fk.delete_referential_action = 2 THEN ‘ ON DELETE SET NULL’
                WHEN fk.delete_referential_action = 3 THEN ‘ ON DELETE SET DEFAULT’
                ELSE "
              END
            + CASE
                WHEN fk.update_referential_action = 1 THEN ‘ ON UPDATE CASCADE’
                WHEN fk.update_referential_action = 2 THEN ‘ ON UPDATE SET NULL’
                WHEN fk.update_referential_action = 3 THEN ‘ ON UPDATE SET DEFAULT’ 
                ELSE "
              END
            + CHAR(13) + ‘ALTER TABLE ‘ + @object_name + ‘ CHECK CONSTRAINT [‘ + fk.name  + ‘]’ + CHAR(13)
        FROM CPADW.sys.foreign_keys fk WITH (NOWAIT)
        JOIN CPADW.sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id
        WHERE fk.parent_object_id = @object_id
        FOR XML PATH(N"), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)), “)
    + ISNULL(((SELECT
         CHAR(13) + ‘CREATE’ + CASE WHEN i.is_unique = 1 THEN ‘ UNIQUE’ ELSE " END
                + ‘ NONCLUSTERED INDEX [‘ + i.name + ‘] ON ‘ + @object_name + ‘ (‘ +
                STUFF((
                SELECT ‘, [‘ + c.name + ‘]’ + CASE WHEN c.is_descending_key = 1 THEN ‘ DESC’ ELSE ‘ ASC’ END
                FROM index_column c
                WHERE c.is_included_column = 0
                    AND c.index_id = i.index_id
                FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “) + ‘)’ 
                + ISNULL(CHAR(13) + ‘INCLUDE (‘ +
                    STUFF((
                    SELECT ‘, [‘ + c.name + ‘]’
                    FROM index_column c
                    WHERE c.is_included_column = 1
                        AND c.index_id = i.index_id
                    FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, “) + ‘)’, “)  + CHAR(13)
        FROM CPADW.sys.indexes i WITH (NOWAIT)
        WHERE i.[object_id] = @object_id
            AND i.is_primary_key = 0
            AND i.[type] = 2
        FOR XML PATH(“), TYPE).value(‘.’, ‘NVARCHAR(MAX)’)
    ), “)
set @SQLScript=@SQL

建立用來移轉帳號的 T-SQL 語法

SELECT    CONCAT(‘CREATE LOGIN [‘, name, ‘] WITH PASSWORD=’, CONVERT(varchar(256),password_hash,1), ‘ HASHED,SID=’,CONVERT(varchar(85),sid,1),’;’)
FROM    sys.sql_logins
WHERE    name IN (‘SQL 自家帳號 1′,’SQL 自家帳號 2’);

從 Oracle 轉 SQL 資料發生 PK 重複

透過 Linked Server 回 Oracle 查失敗的紀錄,在 Oracle 的資料表內顯然有使用者輸入時,可以敲入 FF 導致眼睛看不到,但卻重複兩筆的 PK

select * from openquery(LinkedServer,’select utl_raw.cast_to_raw(PKCol) b, PKCol from Schema.Table where PKCol like “%DuplicateKey%"‘)

image

無題,效能調教標本

純粹留紀念

image

串接授權與拒絕

簡單範例:

use tempdb
create table t(c1 int)
create user u without login
create user u2 without login
grant select on t to u with grant option
exec(‘grant select on t to u2 as u’) as user=’u’
exec(‘select * from t’) as user=’u2′
deny select on t to u cascade
/*
Msg 229, Level 14, State 5, Line 18
結構描述 ‘dbo’,資料庫 ‘tempdb’,物件 ‘t’ 沒有 SELECT 權限。
*/
exec(‘select * from t’) as user=’u2′

drop table t
drop user u
drop user u2

透過 SSMS 可以看到授予者

image

透過 PowerShell 讀 MSMQ,以及在資料庫內,跨資料表與欄位找尋是否包含某個字串

朋友的需求:

$q= new-object System.Messaging.MessageQueue ‘機器名稱\private$\erptowf’
$msg=$q.Peek()

$s=new-object System.IO.StreamReader $msg.BodyStream

$s.ReadToEnd()

 

declare cur cursor
for select schema_name(o.schema_id) schName, object_name(c.object_id) tbName,c.name from sys.columns c
join sys.objects o on c.object_id=o.object_id
where TYPE_NAME(user_type_id) in (‘varchar’,’nvarchar’,’char’,’nchar’) and o.type=’u’ and o.is_ms_shipped=0

declare @sch sysname,@tb sysname,@col sysname
create table t(schemaname sysname,tablename sysname,columnname sysname)
open cur
fetch cur into @sch,@tb,@col
declare @sql nvarchar(max), @search nvarchar(100)=’58.’
while @@FETCH_STATUS =0
begin
    set @sql=’if exists(select * from [‘ + @sch + ‘].[‘ + @tb + ‘] where [‘ + @col + ‘] like “%’ + @search + ‘%") insert t values(“‘ +@sch + “‘,"‘ + @tb + “‘,"‘ + @col + “‘)’
    –print @sql
    exec(@sql)
    fetch cur into @sch,@tb,@col
end
close cur
deallocate cur
select * from t
drop table t

SQL Server 2016 的交易記錄傳送可能需要手動安裝 .NET Framework 3.5

交易記錄傳送實際上是透過各SQL Server執行個體上的作業呼叫「sqllogship.exe」應用程式,需要小心的是在安裝SQL Server 2016時並未要求一定需要.NET Framework 3.5,而sqllogship.exe工具程式需要.NET Framework 3.5,導致執行失敗卻未將失敗原因寫入作業記錄。

透過命令提示列呼叫sqllogship.exe才會出現缺少.NET Framework 3.5的錯誤訊息。

先用 SSMS 2016 英文版為 SQL Server 2016 中文版設定 AG

到目前為止 SSMS 2016(2016 年 7 或 8 月號,網路 download 寫的是 7 月號,但安裝完程式 show 的是 8 月號)中文版建置 AG 的精靈有 Bug,但可以用英文版 SSMS 幫中文版 SQL Server 建置與設定。