需要一個有效的常式來傳遞對象名稱，有或沒有“‘和__‘‘一種nd’’ and ’’ 括號，獲取 ObjectID

有人在他們的智慧中創建了一張名為user：

select schema_name(schema_id)
,*
from sys.tables
where type = 'U'
and name like '%user%'

select top 100 *
from usr.user

消息 156，級別 15，狀態 1，第 14 行關鍵字“使用者”附近的語法不正確。

我有很多腳本在其中傳遞object name，我需要檢查傳遞的名稱在哪裡有效（存在於目前數據庫中）。

usr.user當我將表名作為參數傳遞時，有些失敗了。

所以我看看 sql server 是如何做到這一點的，sp_help作為一個例子

我想出了這樣的東西：

--=======================================================
-- check for the existence of object name or data type (system or user)
-- within the current database
-- works on sql server 2016 but probably lower versions too
-- marcelo miorelli
-- 18-july-2017
--=======================================================
SET NOCOUNT ON

declare @objname varchar(108) = NULL  -- object name we're after  
declare @dbname varchar(108) = NULL  -- object name we're after  

select @objname = 'ipv4' -- this is the parameter to be passed

-- Make sure the @objname is local to the current database.  
select @dbname = parsename(@objname,3)  
if @dbname is null  
 select @dbname = db_name()  
else if @dbname <> db_name()  
 begin  
  raiserror(15250,-1,-1)  
  --return(1)  
 end  

-- @objname must be either sysobjects or systypes: first look in sysobjects  

declare @objid int  
declare @sysobj_type char(2)  

select @objid = object_id, 
       @sysobj_type = type 
  from sys.all_objects 
 where object_id = object_id(@objname)  

-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --  
if @objid is null  
begin  
 -- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME  
 select @objid = type_id(@objname)  

 -- IF NOT IN SYSTYPES, GIVE UP 
 -- in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects) 
 -- and it's recommended as a best practice to use the views instead, 
 -- sys.columns, sys.objects, sys.types, etc 

 if @objid is null  
 begin  
  raiserror(15009,-1,-1,@objname,@dbname)  
  --return(1)  
 end  
end 

select [@objname] = @objname
     ,[@objid] = @objid
     ,[@sysobj_type]=@sysobj_type
     ,[@dbname]=@dbname

usr.[user]作為參數 的範例

usr.user作為參數

我什至開始創建一個使用者定義的數據類型只是為了測試 ipv4

問題：有沒有更好（性能方面）\更標準的方法來完成這項工作？

一些注意事項：

1. 您可以PARSENAME從現有sys.objects查詢中刪除並獲取對象名稱，因為您使用OBJECT_ID()內置函式來處理獲取對象，而不管它是如何命名的。
2. 擺脫PARSENAME意味著您不會處理數據庫名稱，但問題中原始程式碼頂部的註釋確實表明您只關心檢查“在目前數據庫中”。
3. JOIN然後，您可以sys.schemas同時獲取 Schema 名稱。
4. 將 SchemaName 和 ObjectName 連接在一起非常容易，因此請將它們分開，以便您可以在需要時處理這些部分。
5. 您不需要/想要一個 IF 塊來檢查類型，因為它忽略了邊緣情況。對象和類型位於兩個單獨的表中，因此它們之間的名稱沒有強制唯一性。這意味著，駐留在 中的任何模式綁定對像sys.objects 都可以與 中的類型具有相同的名稱sys.types。您需要處理對象和類型都具有相同架構和名稱的情況。
6. 架構名稱和對象/類型名稱應通過QUOTENAME內置函式括在方括號中。
7. 有四種不同類型的類型：系統、使用者定義的數據類型 (UDDT)、SQLCLR 使用者定義的類型 (UDT) 和使用者定義的表類型 (UDTT)。您可能需要區分這些，因為它們的處理方式並不總是相同。
8. 始終NVARCHAR用於系統中任何事物的名稱：對象、模式、列、類型、索引、作業、程序集、隊列、數據庫、登錄名、伺服器等。切勿使用VARCHAR. 大多數名稱被定義為類型sysname，它只是.master``NVARCHAR(128)

處理單部分名稱時，請使用sysname（在少數情況下sysname未使用，但這通常應該始終有效，因為大多數名稱無論如何都不會變得那麼大）。在處理多部分名稱時，您需要增加大小以佔每個部分加上方括號加上連接句點/點。在這裡，我們正在處理 SchemaName + ObjectName，因此每個“名稱”有 128 個 + 4 個方括號 + 1 個點之間的點 = 最多 261 個字元。如果您還需要考慮數據庫名稱，那將是另外 131 個字元 (128 + 2 + 1)。

NVARCHAR使用而不是的原因VARCHAR是：

1. 在最基本的層面上，這應該是為了程式碼中的數據類型一致性。由於名稱都是內部NVARCHAR的，因此 usingVARCHAR會導致這些參數/變數/列的任何地方顯式轉換（如果將名稱儲存在表中）。
2. 由於系統中的所有名稱都在內部儲存為NVARCHAR，因此在任何名稱中使用任何 Unicode 字元在技術上是可以的，包括組合字元、補充字元等。是的，有一個注意事項，補充字元在數據庫中不起作用名稱，但它們實際上是 ;-)（在這種情況下需要處理一些細微差別）。因此，如果您使用VARCHAR參數/變數，您可能是名稱的錯位版本，而不是名稱本身，這會產生奇怪的行為。

下面的內聯表值函式 (ITVF) 封裝了上述所有建議。如果需要，系統類型可以進一步分解為基本類型（INT、VARCHAR等）和 CLR 類型（HierarchyID、Geometry和Geography），但在這裡它們都只是系統“類型”。

內聯-TVF

CREATE FUNCTION dbo.GetObjectInfo (@Name NVARCHAR(261))
RETURNS TABLE
--WITH SCHEMABINDING -- cannot schema bind due to referencing system objects
AS RETURN
SELECT obj.[object_id],
      QUOTENAME(sch.[name]) AS [SchemaName],
      QUOTENAME(obj.[name]) AS [ObjectName],
      obj.[type]
FROM   sys.all_objects obj WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
       ON sch.[schema_id] = obj.[schema_id]
WHERE  obj.[object_id] = OBJECT_ID(@Name)
UNION ALL
SELECT typ.[user_type_id],
      QUOTENAME(sch.[name]) AS [SchemaName],
      QUOTENAME(typ.[name]) AS [ObjectName],
      CASE typ.[is_user_defined]
        WHEN 1 THEN CASE
                      WHEN typ.[is_assembly_type] = 1 THEN 'UDT'
                      WHEN typ.[is_table_type] = 1 THEN 'UDTT'
                      ELSE 'UDDT'
                    END
        ELSE 'Type'
      END AS [type]
FROM   sys.types typ WITH (NOLOCK)
INNER JOIN sys.schemas sch WITH (NOLOCK)
       ON sch.[schema_id] = typ.[schema_id]
WHERE  typ.[user_type_id] = TYPE_ID(@Name);
GO

測試

USE [tempdb];
CREATE TABLE dbo.[user] (Col1 INT);
CREATE TYPE dbo.[user] FROM INT NOT NULL;
CREATE TYPE dbo.[table] AS TABLE (Col1 INT);

SELECT * FROM dbo.GetObjectInfo(N'dbo.user'); -- returns 2 rows
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[user]'); -- returns 2 rows

SELECT * FROM dbo.GetObjectInfo(N'dbo.table'); -- returns 1 row
SELECT * FROM dbo.GetObjectInfo(N'[dbo].[table]'); -- returns 1 row

引用自：https://dba.stackexchange.com/questions/180230