SQL Server - 为 sysobjects 返回 SCHEMA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/917431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Return SCHEMA for sysobjects
提问by Gerhard Weiss
How to I get the SCHEMA when doing a select on sysobjects?
在 sysobjects 上进行选择时如何获得 SCHEMA？
I am modifing a stored procedure called SearchObjectsForTextwhich returns only the Name but I would also like to include the SCHEMA.
我正在修改一个名为SearchObjectsForText的存储过程，它只返回名称，但我也想包括 SCHEMA。
Right now it is doing something similar to this:
现在它正在做类似的事情：
SELECT DISTINCT name
FROM sysobjects
I would like to know what tables need to be joined to return the SCHEME for each 'name'.
我想知道需要加入哪些表才能返回每个“名称”的 SCHEME。
回答by devio
If you mean SQL Server 2005 or higher, use sys.objects instead of sysobjects:
如果您的意思是 SQL Server 2005 或更高版本，请使用 sys.objects 而不是 sysobjects：
SELECT sys.objects.name, sys.schemas.name AS schema_name
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
2005 introduced schemas. up to 2000, users equaled schemas. The same query for SQL Server 2000:
2005 年引入了模式。直到 2000 年，用户等于模式。SQL Server 2000 的相同查询：
SELECT sysusers.name AS OwnerName, sysobjects.name
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
回答by wire science
On Sql Server 2005 (and above) you can use the sys.objects view:
在 Sql Server 2005（及更高版本）上，您可以使用 sys.objects 视图：
select
name as ObjectName,
schema_Name(schema_id) as SchemaName
from
sys.objects
In Sql Server 2000 (and below), "schema" had a different conceptual meaning. Note from MSDN:
在 Sql Server 2000（及更低版本）中，“架构”具有不同的概念含义。来自 MSDN 的注释：
In earlier releases of SQL Server, databases could contain an entity called a "schema", but that entity was effectively a database user. SQL Server 2005 is the first release of SQL Server in which a schema is both a container and a namespace.
在 SQL Server 的早期版本中，数据库可能包含一个称为“架构”的实体，但该实体实际上是数据库用户。SQL Server 2005 是 SQL Server 的第一个版本，其中架构既是容器又是命名空间。
回答by bdukes
Could you use the Information_Schema view(s)instead?
您可以改用Information_Schema 视图吗？
SELECT DISTINCT table_name, table_schema
FROM INFORMATION_SCHEMA.TABLES
According to the MSDN page(for SQL Server 2008 and above),
根据MSDN 页面（对于 SQL Server 2008 及更高版本），
Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view.
不要使用 INFORMATION_SCHEMA 视图来确定对象的架构。查找对象架构的唯一可靠方法是查询 sys.objects 目录视图。
However, it seems that they're probably referring to an issue where you have a table name and are trying to find its schema, which wouldn't work if there were multiple tables with the same name (in different schemas). If you're querying for multiple results (not just trying to find the schema for a specific table), then it should be fine.
但是，似乎他们可能指的是您有一个表名并试图找到它的架构的问题，如果有多个具有相同名称的表（在不同的架构中），这将不起作用。如果您要查询多个结果（不仅仅是尝试查找特定表的架构），那么应该没问题。
回答by marc_s
I would favor using the more focused "sys" views - sys.procedures instead of sys.objects. You'll need to join it with the sys.schemas view to get schema name and such.
我更喜欢使用更集中的“系统”视图 - sys.procedures 而不是 sys.objects。您需要将它与 sys.schemas 视图结合以获取架构名称等。
select
p.name,
s.name 'Schema',
p.type_desc, p.create_date, p.modify_date
from
sys.procedures p
inner join
sys.schemas s ON p.schema_id = s.schema_id
I would start to get away from using "sysobjects" since Microsoft clearly states in Books Online that "sysobjects" is subject to removal in a future release:
我将开始避免使用“sysobjects”，因为 Microsoft 在 Books Online 中明确指出“sysobjects”将在未来版本中被删除：
This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
此 SQL Server 2000 系统表作为向后兼容性视图包含在内。我们建议您改用当前的 SQL Server 系统视图。若要查找等效的系统视图或视图，请参阅将 SQL Server 2000 系统表映射到 SQL Server 2005 系统视图。此功能将在 Microsoft SQL Server 的未来版本中删除。避免在新的开发工作中使用此功能，并计划修改当前使用此功能的应用程序。
Marc
马克
回答by Mike Gledhill
Just to repeat what's already been suggested here, here's what I've used, to get a list of Tables, Stored Procedures, Views and Functions in my database:
只是重复这里已经建议的内容，这是我使用过的，在我的数据库中获取表、存储过程、视图和函数的列表：
SELECT schema_Name(schema_id) as SchemaName,
[name], -- Name of the Table, Stored Procedure or Function
[type] -- 'V' for Views, 'U' for Table, 'P' for Stored Procedure, 'FN' for function
FROM sys.objects
WHERE [type_desc] IN ( 'USER_TABLE', 'SQL_STORED_PROCEDURE', 'VIEW', 'SQL_SCALAR_FUNCTION')
AND [name] NOT LIKE 'sp_%'
AND [name] NOT LIKE 'fn_%'
ORDER BY 3 DESC, -- type first
1 ASC, -- then schema
2 ASC -- then function/table name
...and here's what our good friend Northwindwould return...
……这就是我们的好朋友Northwind会回来的……
回答by user3691460
Have included an option to delete all objects starting with certain prefix and optionally from certain schema. By the way, I added extra query to get all types which are not stored on sysobjects by default.
已包含一个选项，用于删除以特定前缀开头的所有对象，并可选择从特定架构中删除。顺便说一下，我添加了额外的查询来获取默认情况下未存储在 sysobjects 上的所有类型。
I have uploaded entire sample script to GitHub: DropAll_Dnn_Objects.sql
我已将整个示例脚本上传到 GitHub： DropAll_Dnn_Objects.sql
Part 1: Temporary Stored Procedure:
第 1 部分：临时存储过程：
IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
DROP PROCEDURE _temp_DropAllDnnObjects;
GO
CREATE PROCEDURE _temp_DropAllDnnObjects
@object_prefix NVARCHAR(30),
@schema_name sysname = NULL
AS
BEGIN
DECLARE @sname sysname, @name sysname, @type NVARCHAR(30)
DECLARE @object_type NVARCHAR(255), @sql NVARCHAR(2000), @count INT = 0
DECLARE curs CURSOR FOR
SELECT sname, [name], xtype
FROM (
SELECT SCHEMA_NAME(schema_id) as sname, [name], [type] as xtype
FROM sys.objects
WHERE [type] IN ('U', 'P', 'FN', 'IF', 'TF', 'V', 'TR')
AND name LIKE @object_prefix + '%'
AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
UNION ALL
SELECT SCHEMA_NAME(schema_id) as sname, [name], 'TYPE' as xtype
FROM sys.types
WHERE is_user_defined = 1
AND [name] LIKE @object_prefix + '%'
AND (@schema_name IS NULL OR schema_id = SCHEMA_ID(@schema_name))
) a
ORDER BY CASE xtype
WHEN 'P' THEN 1
WHEN 'FN' THEN 2
WHEN 'IF' THEN 3
WHEN 'TF' THEN 4
WHEN 'TR' THEN 5
WHEN 'V' THEN 6
WHEN 'U' THEN 7
WHEN 'TYPE' THEN 8
ELSE 9
END, name
OPEN curs;
FETCH NEXT FROM curs INTO @sname, @name, @type;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @count = @count + 1
-- Configuration point 2
SET @object_type = CASE @type
WHEN 'P' THEN 'PROCEDURE'
WHEN 'FN' THEN 'FUNCTION'
WHEN 'IF' THEN 'FUNCTION'
WHEN 'TF' THEN 'FUNCTION'
WHEN 'TR' THEN 'TRIGGER'
WHEN 'V' THEN 'VIEW'
WHEN 'U' THEN 'TABLE'
WHEN 'TYPE' THEN 'TYPE'
END
SET @sql = REPLACE(REPLACE(REPLACE('DROP <TYPE> [<SCHEMA>].[<NAME>];',
'<TYPE>', @object_type),
'<SCHEMA>', @sname),
'<NAME>', @name)
BEGIN TRY
PRINT @sql
EXEC(@sql)
END TRY
BEGIN CATCH
PRINT 'ERROR: ' + ERROR_MESSAGE()
END CATCH
FETCH NEXT FROM curs INTO @sname, @name, @type;
END;
PRINT CONCAT('Objects Found: ', @Count)
PRINT ''
PRINT '------------------------------------------------------'
PRINT ''
CLOSE curs;
DEALLOCATE curs;
RETURN @Count
END;
GO
It will continue on errors (and display the error message). It will return a count of all objects found.
它将继续出现错误（并显示错误消息）。它将返回找到的所有对象的计数。
Part 2: Call Stored Procedure with parameters:
第 2 部分：使用参数调用存储过程：
You can create a WHILE loop in order to run the command until no object is left (dependencies), as follows:
您可以创建 WHILE 循环以运行该命令，直到没有对象（依赖项）为止，如下所示：
DECLARE @count INT = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'dnn';
SET @count = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'aspnet';
SET @count = 1
WHILE @count > 0 EXEC @count = _temp_DropAllDnnObjects 'vw_aspnet';
GO
Part 3: Finally, get rid of the procedure:
第 3 部分：最后，摆脱程序：
IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
DROP PROCEDURE _temp_DropAllDnnObjects;
GO
回答by CA Martin
Instead of a view, why not use this to populate a temporary table you can use?
除了视图，为什么不使用它来填充您可以使用的临时表？
This is the solution I use in stored procedures
这是我在存储过程中使用的解决方案
This is the best way to get a schema dynamically and add it to the different tables within a database in order to get other information dynamically
这是动态获取模式并将其添加到数据库中的不同表中以便动态获取其他信息的最佳方式
select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'
select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'
exec (@sql)
of course #tables is a dynamic table in the stored procedure
当然#tables是存储过程中的动态表
回答by Carlos Alvarado Andrade
In SQL 200:
在 SQL 200 中：
select DISTINCT
name as ObjectName,
USER_NAME(uid) as SchemaName
from
sysobjects
In earlier releases of SQL Server, databases could contain an entity called a "schema", but that entity was effectively a database user.
在 SQL Server 的早期版本中，数据库可能包含一个称为“架构”的实体，但该实体实际上是数据库用户。
