備忘：創建Link Server/源代碼導出/斷片化調查等

1，創建Link Server
IF  EXISTS (SELECT * FROM sys.servers WHERE name = N'svr101')
    EXECUTE sp_dropserver N'svr101', 'droplogins';
GO

DECLARE @servername SYSNAME;
SET     @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName'));

     EXECUTE sys.sp_addlinkedserver
            @server = N'svr101',
            @srvproduct = N'',
            @provider = N'SQLNCLI', 
            @datasrc = @servername;
           
EXECUTE sys.sp_serveroption
            @server = N'svr101',
            @optname = 'RPC OUT',
            @optvalue = 'ON';

EXEC sp_addlinkedsrvlogin 'svr101', 'false',NULL , 'user_name', 'password'

-- 刪除
-- EXEC sp_droplinkedsrvlogin 'svr101', NULL
-- sp_dropserver 'svr101', 'droplogins'

-- 使用例
select * from [svr101].DATA_DB.dbo.TB_xxx 

2,源代碼導出
select   CHAR(13) + N'==[' + pros.name + N']===PROCEDURE_NAME===========' + CHAR(13)+ REPLICATE(mods.definition,1) + CHAR(13)+ N'==[' + pros.name + N']===PROCEDURE_NAME_END=========' + CHAR(13)
 from  sys.procedures pros inner join sys.all_sql_modules mods 
 on pros.object_id = mods.object_id
 order by LEN(mods.definition) desc

3,斷片化調查
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'table_name'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

4, OBJECT名
SELECT OBJECT_SCHEMA_NAME([object_id]),
    OBJECT_NAME([object_id])
    FROM sys.partitions
    WHERE partition_id =  72057647487385600