代码之家  ›  专栏  ›  技术社区  ›  Maslow

如何在涉及sys表的两个数据库之间联接查询?

  •  0
  • Maslow  · 技术社区  · 14 年前
    -- find last usage info, 
    -- how far back this information goes depends on the 
    -- server/database settings
    
    select --
    ss.name as SchemaName, so.name as SprocName
    ,so.create_date as SprocCreated, so.modify_date as SprocModified 
    ,so.object_id
    ,stat.last_user_seek,stat.last_user_scan,stat.last_user_lookup,stat.last_user_update
    ,stat.last_system_seek,stat.last_system_scan,stat.last_system_lookup, stat.last_system_update
    from sys.objects so
    
    inner join sys.schemas ss on so.schema_id=ss.schema_id
    
    left join sys.dm_db_index_usage_stats stat
    on so.object_id=stat.object_id
    
    where ss.name<>'sys' and so.type='P' 
    union 
    select 
    ss.name as SchemaName, so.name as SprocName
    ,so.create_date as SprocCreated, so.modify_date as SprocModified 
    ,so.object_id
    ,stat.last_user_seek,stat.last_user_scan,stat.last_user_lookup,stat.last_user_update
    ,stat.last_system_seek,stat.last_system_scan,stat.last_system_lookup, stat.last_system_update
    from db_dit.dbo.sys.objects so 
    
    inner join sys.schemas ss on so.schema_id=ss.schema_id
    
    left join sys.dm_db_index_usage_stats stat
    on so.object_id=stat.object_id
    
    where ss.name<>'sys' and so.type='P'
    order by case when stat.object_id is null then 0 else 1 end,ss.name,so.name
    

    我试图验证dev的存储过程更改是否被推到了一个简单的查询中,但我似乎无法在另一个数据库上查询sys表 Invalid object name 'db_dit.dbo.sys.objects'.

    1 回复  |  直到 14 年前
        1
  •  2
  •   SQLMenace    14 年前

    而不是 db_dit.dbo.sys.objects db_dit.sys.objects

    sys 已经是架构,不需要 dbo

    例子

    select * from tempdb.sys.objects s1
        join msdb.sys.objects s2 on s1.name = s2.name