Although I cant find a way to gather all the information shown in the GUI with T-SQL for Linked Servers you can still get an alwful lot and also a lot of informaton that you cant get via the GUI.

Hope you find this useful.

/*

Linked Servers

*/
SELECT * FROM sys.linked_logins
-- List all Linked Server on Instance
SELECT  name ,
        product ,
        is_remote_login_enabled ,
        is_rpc_out_enabled ,
        is_collation_compatible ,
        remote_name AS 'remote_login'
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
--LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
WHERE is_linked = 1

-- SP to list all Linked Servers
exec sp_linkedservers

-- List databases on Linked Server
sp_catalogs @server_name = 'NWPAPPSQL05A'

-- List Tables on linked Server database
-- http://technet.microsoft.com/en-us/library/aa260332%28v=sql.80%29.aspx
EXEC sp_tables_ex @table_server = 'NWPAPPSQL05A' 
	,@table_catalog='NovaReporting' -- Only gather table information on specific database
	,@table_schema='dbo' -- Only gather table information on specific schema
	,@table_name='Suppliers' -- Only gather table information on specific table

-- List Permissions on Linked Server
-- http://msdn.microsoft.com/en-us/library/ms188327.aspx
EXEC sp_table_privileges_ex @table_server = 'NWPAPPSQL05A'
	,@table_catalog='NovaReporting' -- Only gather permissions on specific database
	,@table_schema='dbo' -- Only gather permissions on specific schema
	,@table_name='Suppliers' -- Only gather permissions on specific table

-- List column information on Linked Server
-- http://technet.microsoft.com/en-us/library/aa259634(v=sql.80).aspx
EXEC sp_columns_ex @table_server = 'NWPAPPSQL05A'
	,@table_catalog='NovaReporting' -- Only gather permissions on specific database
	,@table_schema='dbo' -- Only gather permissions on specific schema
	,@table_name='Suppliers' -- Only gather permissions on specific table


-- RPC OUT = enable to run stored procedures on Linked Server 
-- RPC Out Example - Run EXEC @sql and exec sp_xxx
-- RPC Example - doesnt do anything http://msgroups.net/microsoft.public.sqlserver.server/rpc-vs-rpc-out-for-linked-s/98628
-- http://www.sqlservercentral.com/Forums/Topic1313539-1550-1.aspx 


-- Linked Server Options 
-- http://technet.microsoft.com/en-us/library/ms186839(v=sql.105).aspx


-- Find when Linked Server was last used:
-- http://dba.stackexchange.com/questions/5519/determine-last-usage-date-of-a-linked-server
set transaction isolation level read uncommitted

SELECT
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1)) 
        AS sql_statement,
    last_execution_time
FROM sys.dm_exec_query_stats AS s1 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE 
    s2.text like '%Linked Server%' -- Linked Server Name 
    --and last_execution_time > convert(datetime, '2011-01-01 00:00:00.000', 121)
ORDER BY 
    s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset



-- View linked server dependencies 
-- http://stackoverflow.com/questions/6451566/view-linked-server-dependencies-sql-server-2008
USE Master;
GO

IF OBJECT_ID('tempdb..#Deps') IS NOT NULL
    BEGIN
        DROP TABLE #Deps
    END

CREATE TABLE #Deps
    (
      [ServerName] [VARCHAR](500) NOT NULL ,
      [DatabaseName] [VARCHAR](500) NOT NULL ,
      [SchemaName] [VARCHAR](500) NOT NULL ,
      [ObjectName] [VARCHAR](MAX) NULL ,
      [ObjectId] [INT] NOT NULL ,
      [ObjectType] [VARCHAR](500) NOT NULL ,
      [DependsOnLinkedServer] [VARCHAR](500) NOT NULL ,
      [definition] [VARCHAR](MAX) NULL
    )

IF OBJECT_ID('tempdb..#Queries') IS NOT NULL
    BEGIN
        DROP TABLE #Queries
    END

SELECT  REPLACE('INSERT INTO #Deps 
     (
       [ServerName]       
       ,[DatabaseName]
       ,[SchemaName]
       ,[ObjectName]
       ,[ObjectType]
       ,[ObjectId]
       ,[DependsOnLinkedServer]
       ,[definition]
     )
     SELECT
       @@SERVERNAME,
       ''?'' AS DatabaseName,
       s.name AS SchemaName,
       o.name AS ObjectName,
       o.type_desc AS ObjectType,
       m.object_id AS ObjectId,
       ''' + srv.name + ''' AS DependsOnLinkedServer,
       m.definition
     FROM
       [?].sys.sql_modules m
       LEFT OUTER JOIN [?].sys.objects o
       ON m.object_id = o.object_id
       LEFT OUTER JOIN [?].sys.schemas s
       ON o.schema_id = s.schema_id
     WHERE     
       definition LIKE ''%' + srv.name + '%''', CHAR(13) + CHAR(10), '') AS Query
INTO    #Queries
FROM    sys.servers srv;
GO

DECLARE @Query AS VARCHAR(MAX)
DECLARE LinkedServerCursor CURSOR FAST_FORWARD
FOR
    SELECT  Query
    FROM    #Queries

OPEN LinkedServerCursor
FETCH NEXT FROM LinkedServerCursor INTO @Query;

WHILE @@FETCH_STATUS = 0
    BEGIN  
        EXECUTE master.sys.sp_MSforeachdb @Query

        FETCH NEXT FROM LinkedServerCursor INTO @Query;
    END

CLOSE LinkedServerCursor;
DEALLOCATE LinkedServerCursor; 

GO

SELECT  ServerName ,
        DatabaseName ,
        ObjectName ,
        '[' + ServerName + '].[' + DatabaseName + '].[' + SchemaName + '].['
        + ObjectName + ']' AS QualifiedObjectName ,
        DependsOnLinkedServer ,
        ObjectType
FROM    #Deps
ORDER BY ServerName ,
        DatabaseName ,
        ObjectName





Advertisements