如何在另一个数据库中创建引用调用者中正确的 sys.objects 表的 UDF 或视图？

发布于 2024-09-29 23:05:30 字数 551 浏览 7 评论 0原文

使用 SQL Server 2008，我想创建一个 UDF，为我提供对象的创建日期。这是代码：

create function dbo.GetObjCreateDate(@objName sysname) returns datetime as
begin
    declare @result datetime
    select @result = create_date from sys.objects where name = @objname
    return @result
end
go

我想将此 UDF 放入主数据库或其他一些共享数据库中，以便可以从任何地方访问它，除非我这样做，然后 sys.objects 引用将被拉出来自 master 数据库，而不是我从中发起查询的数据库。我知道您可以执行此操作，因为 information_schema 视图位于 master 中，并且只需包装对 sys.objects 本地实例的调用，因此我希望有一种简单的方法可以做到我的 UDF 也是如此。

原文

Using SQL Server 2008, I'd like to create a UDF that gives me the create date of an object. This is the code:

create function dbo.GetObjCreateDate(@objName sysname) returns datetime as
begin
    declare @result datetime
    select @result = create_date from sys.objects where name = @objname
    return @result
end
go

I'd like to put this UDF in the master database or some other shared database so that it is accessible from anywhere, except that if I do that then the sys.objects reference pulls from the master database instead of the database that I'm initiating my query from. I know you can do this as the information_schema views sit in master and just wrap calls to local instances of sys.objects, so I'm hoping there's a simple way to do that with my UDF as well.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清秋悲枫 2024-10-06 23:05:30

试试这个：

CREATE FUNCTION dbo.GetObjCreateDate(@objName sysname, @dbName sysname) 
RETURNS datetime AS
BEGIN
    DECLARE @createDate datetime;
    DECLARE @params nvarchar(50);
    DECLARE @sql nvarchar(500);

    SET @params = '@createDate datetime OUTPUT';

    SELECT @sql = 'SELECT @createDate = create_date FROM ' + @dbName + '.sys.objects WHERE name = ''' + @objname + '''';

    EXEC sp_executesql @sql, @params, @createDate = @createDate OUTPUT;         

     RETURN @createDate
END
;

Try this:

CREATE FUNCTION dbo.GetObjCreateDate(@objName sysname, @dbName sysname) 
RETURNS datetime AS
BEGIN
    DECLARE @createDate datetime;
    DECLARE @params nvarchar(50);
    DECLARE @sql nvarchar(500);

    SET @params = '@createDate datetime OUTPUT';

    SELECT @sql = 'SELECT @createDate = create_date FROM ' + @dbName + '.sys.objects WHERE name = ''' + @objname + '''';

    EXEC sp_executesql @sql, @params, @createDate = @createDate OUTPUT;         

     RETURN @createDate
END
;

回复收藏 0 原文

浅紫色的梦幻 2024-10-06 23:05:30

为什么不这样做呢？

创建一个存储过程，在 master 数据库中创建一个视图，其中包含服务器上每个数据库的 sys.objects 中的所有信息。
创建一个 DDL 触发器，每当 CREATE、ALTER 或 DROP 语句。然后触发器将执行步骤 #1 中的存储过程。这允许视图自动更新。
（可选） 创建一个用户定义的函数，用于在视图中查询给定对象的创建日期。

存储过程 DDL：

USE [master];
GO

CREATE PROCEDURE dbo.BuildAllServerObjectsView
AS

SET NOCOUNT ON;

IF OBJECT_ID('master.dbo.AllServerObjects') IS NOT NULL
    EXEC master..sp_SQLExec 'DROP VIEW dbo.AllServerObjects;';

IF OBJECT_ID('tempdb..Databases') IS NOT NULL
    DROP TABLE #Databases;

DECLARE @CreateView varchar(8000);
SET @CreateView = 'CREATE VIEW dbo.AllServerObjects AS' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);

SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS AS 'name'
  INTO #Databases
  FROM sys.databases
 ORDER BY name;

DECLARE @DatabaseName nvarchar(100);
WHILE (SELECT COUNT(*) FROM #Databases) > 0
BEGIN
    SET @DatabaseName = (SELECT TOP 1 name FROM #Databases ORDER BY name);
    SET @CreateView +='SELECT N'+QUOTENAME(@DatabaseName, '''')+' AS ''database_name''' + CHAR(13)+CHAR(10)
                    + '      ,name COLLATE SQL_Latin1_General_CP1_CI_AS AS ''object_name''' + CHAR(13)+CHAR(10)
                    + '      ,object_id' + CHAR(13)+CHAR(10)
                    + '      ,principal_id' + CHAR(13)+CHAR(10)
                    + '      ,schema_id' + CHAR(13)+CHAR(10)
                    + '      ,parent_object_id' + CHAR(13)+CHAR(10)
                    + '      ,type' + CHAR(13)+CHAR(10)
                    + '      ,type_desc' + CHAR(13)+CHAR(10)
                    + '      ,create_date' + CHAR(13)+CHAR(10)
                    + '      ,modify_date' + CHAR(13)+CHAR(10)
                    + '      ,is_ms_shipped' + CHAR(13)+CHAR(10)
                    + '      ,is_published' + CHAR(13)+CHAR(10)
                    + '      ,is_schema_published' + CHAR(13)+CHAR(10)
                    + '  FROM ' + QUOTENAME(@DatabaseName) + '.sys.objects';
    IF (SELECT COUNT(*) FROM #Databases) > 1
        SET @CreateView += CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + ' UNION' + CHAR(13)+CHAR(10);
    ELSE
        SET @CreateView += ';';  

    DELETE #Databases
     WHERE name = @DatabaseName;
END;

--PRINT @CreateView --<== Uncomment this to see the DDL for the view.

EXEC master..sp_SQLExec @CreateView;

IF OBJECT_ID('tempdb..Databases') IS NOT NULL
    DROP TABLE #Databases;

GO

函数 DDL：

USE [master];
GO

CREATE FUNCTION dbo.GetObjCreateDate(@DatabaseName sysname, @objName sysname) RETURNS DATETIME AS
BEGIN
    DECLARE @result datetime;

    SELECT @result = create_date
      FROM master.dbo.AllServerObjects
     WHERE [database_name] = @DatabaseName
       AND [object_name] = @objname;

    RETURN @result;
END
GO

示例用法：

SELECT master.dbo.GetObjCreateDate('MyDatabase', 'SomeObject') AS 'Created';
SELECT master.dbo.GetObjCreateDate(DB_NAME(), 'spt_monitor') AS 'Created';

Why not do this instead?

Create a stored procedure that creates a view in the master database containing all of the information in sys.objects from each database on the server.
Create a DDL Trigger that gets fired whenever a CREATE, ALTER or DROP statement is executed for a database. The trigger would then execute the stored procedure in step #1. This allows the view to be automatically updated.
(Optional) Create a user defined function that queries the view for the creation date of a given object.

Stored Procedure DDL:

USE [master];
GO

CREATE PROCEDURE dbo.BuildAllServerObjectsView
AS

SET NOCOUNT ON;

IF OBJECT_ID('master.dbo.AllServerObjects') IS NOT NULL
    EXEC master..sp_SQLExec 'DROP VIEW dbo.AllServerObjects;';

IF OBJECT_ID('tempdb..Databases') IS NOT NULL
    DROP TABLE #Databases;

DECLARE @CreateView varchar(8000);
SET @CreateView = 'CREATE VIEW dbo.AllServerObjects AS' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10);

SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS AS 'name'
  INTO #Databases
  FROM sys.databases
 ORDER BY name;

DECLARE @DatabaseName nvarchar(100);
WHILE (SELECT COUNT(*) FROM #Databases) > 0
BEGIN
    SET @DatabaseName = (SELECT TOP 1 name FROM #Databases ORDER BY name);
    SET @CreateView +='SELECT N'+QUOTENAME(@DatabaseName, '''')+' AS ''database_name''' + CHAR(13)+CHAR(10)
                    + '      ,name COLLATE SQL_Latin1_General_CP1_CI_AS AS ''object_name''' + CHAR(13)+CHAR(10)
                    + '      ,object_id' + CHAR(13)+CHAR(10)
                    + '      ,principal_id' + CHAR(13)+CHAR(10)
                    + '      ,schema_id' + CHAR(13)+CHAR(10)
                    + '      ,parent_object_id' + CHAR(13)+CHAR(10)
                    + '      ,type' + CHAR(13)+CHAR(10)
                    + '      ,type_desc' + CHAR(13)+CHAR(10)
                    + '      ,create_date' + CHAR(13)+CHAR(10)
                    + '      ,modify_date' + CHAR(13)+CHAR(10)
                    + '      ,is_ms_shipped' + CHAR(13)+CHAR(10)
                    + '      ,is_published' + CHAR(13)+CHAR(10)
                    + '      ,is_schema_published' + CHAR(13)+CHAR(10)
                    + '  FROM ' + QUOTENAME(@DatabaseName) + '.sys.objects';
    IF (SELECT COUNT(*) FROM #Databases) > 1
        SET @CreateView += CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + ' UNION' + CHAR(13)+CHAR(10);
    ELSE
        SET @CreateView += ';';  

    DELETE #Databases
     WHERE name = @DatabaseName;
END;

--PRINT @CreateView --<== Uncomment this to see the DDL for the view.

EXEC master..sp_SQLExec @CreateView;

IF OBJECT_ID('tempdb..Databases') IS NOT NULL
    DROP TABLE #Databases;

GO

Function DDL:

USE [master];
GO

CREATE FUNCTION dbo.GetObjCreateDate(@DatabaseName sysname, @objName sysname) RETURNS DATETIME AS
BEGIN
    DECLARE @result datetime;

    SELECT @result = create_date
      FROM master.dbo.AllServerObjects
     WHERE [database_name] = @DatabaseName
       AND [object_name] = @objname;

    RETURN @result;
END
GO

Sample Usage:

SELECT master.dbo.GetObjCreateDate('MyDatabase', 'SomeObject') AS 'Created';
SELECT master.dbo.GetObjCreateDate(DB_NAME(), 'spt_monitor') AS 'Created';

回复收藏 0 原文

猫烠⑼条掵仅有一顆心 2024-10-06 23:05:30

它必须是一个函数吗？如果您只是希望它可以随处访问，一个技巧是将您的代码放在 varchar 中并对其进行 sp_executesql ：

create procedure dbo.GetObjCreateDate(@objName sysname) 
as
    declare @sql nvarchar(max)
    select @sql = 'select create_date from sys.objects where name = ''' + @objname + ''''
    EXEC sp_executesql @sql 
go

Does it have to be a function? If you just want it accessible everywhere, a trick is to put your code in a varchar and sp_executesql it:

create procedure dbo.GetObjCreateDate(@objName sysname) 
as
    declare @sql nvarchar(max)
    select @sql = 'select create_date from sys.objects where name = ''' + @objname + ''''
    EXEC sp_executesql @sql 
go

回复收藏 0 原文