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

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

user-defined-functions tsql sql-server

mattmc3 · 技术社区 · 16 年前

使用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放在master数据库或其他共享数据库中,这样就可以从任何地方访问它,除非我这样做了,那么 sys.objects 引用从 master information_schema 静观 主人

5 回复 | 直到 14 年前

Alexander Hitesh Bavaliya 14 年前

为什么不这样做呢?

创建一个存储过程,该过程在主数据库中创建一个视图,其中包含 sys.objects 从服务器上的每个数据库。
DDL Trigger 每当 CREATE , ALTER 或 DROP
(可选) 创建一个用户定义的函数,用于查询给定对象的创建日期的视图。

存储过程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';

gotqn user3521065 8 年前

试试这个:

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
;

Jaeell 16 年前

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

KenJ 16 年前

似乎有一个未记录的存储过程允许您创建自己的系统对象: sp_ms_marksystemobject

你可以看更多的 http://www.mssqltips.com/tip.asp?tip=1612

Niladri Biswas 14 年前

看一看 How to Write Your Own System Functions . 我相信这对你有帮助