Как получить SCHEMA при выполнении выбора в sysobjects?
Я модифицирую хранимую процедуру с именем SearchObjectsForText, которая возвращает только имя, но я также хотел бы включить SCHEMA.
Сейчас он делает что-то похожее на это:
SELECT DISTINCT name
FROM sysobjects
Я хотел бы знать, какие таблицы нужно объединить, чтобы вернуть SCHEME для каждого "имени".
Если вы имеете в виду 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 представлены схемы. до 2000, пользователи равнялись схемам. Тот же запрос для SQL Server 2000:
SELECT sysusers.name AS OwnerName, sysobjects.name
FROM sysobjects
INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
На Sql Server 2005 (и выше) вы можете использовать представление sys.objects:
select
name as ObjectName,
schema_Name(schema_id) as SchemaName
from
sys.objects
В Sql Server 2000 (и ниже) "схема" имела другое концептуальное значение. Примечание от MSDN:
В предыдущих выпусках SQL Server базы данных могли содержать объект, называемый "схемой", но этот объект был фактически пользователем базы данных. SQL Server 2005 - это первая версия SQL Server, в которой схема представляет собой контейнер и пространство имен.
Не могли бы вы использовать вид Information_Schema вместо?
SELECT DISTINCT table_name, table_schema
FROM INFORMATION_SCHEMA.TABLES
Согласно странице MSDN (для SQL Server 2008 и выше),
Не используйте представления INFORMATION_SCHEMA для определения схемы объекта. Единственный надежный способ найти схему объекта - запросить представление каталога sys.objects.
Однако похоже, что они, вероятно, относятся к проблеме, в которой у вас есть имя таблицы, и пытаются найти ее схему, которая не будет работать, если бы было несколько таблиц с тем же именем (в разных схемах). Если вы запрашиваете несколько результатов (а не просто пытаетесь найти схему для конкретной таблицы), тогда это должно быть хорошо.
Я бы предпочел использовать более целенаправленные "sys" views - 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
Я бы начал уходить от использования "sysobjects", поскольку Microsoft явно заявляет в Books Online, что "sysobjects" может быть удалена в будущей версии:
Эта системная таблица SQL Server 2000 включена как представление для обратной совместимости. Вместо этого мы рекомендуем использовать текущие системные представления SQL Server. Чтобы найти эквивалентный системный вид или представления, см. "Сопоставление системных таблиц SQL Server 2000 с системными представлениями SQL Server 2005". Эта функция будет удалена в будущей версии Microsoft SQL Server. Избегайте использования этой функции в новых разработках и планируйте изменять приложения, которые в настоящее время используют эту функцию.
Марк
Чтобы повторить то, что уже было предложено здесь, вот что я использовал, чтобы получить список таблиц, хранимых процедур, представлений и функций в моей базе данных:
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
... и вот что наш хороший друг Northwind вернется...
В SQL 200:
select DISTINCT
name as ObjectName,
USER_NAME(uid) as SchemaName
from
sysobjects
В предыдущих выпусках SQL Server базы данных могли содержать объект, называемый "схемой", но этот объект был фактически пользователем базы данных.
Включили возможность удаления всех объектов, начиная с определенного префикса и, необязательно, из определенной схемы. Кстати, я добавил дополнительный запрос для получения всех типов, которые по умолчанию не хранятся в sysobjects.
Я загрузил весь образец script в GitHub: DropAll_Dnn_Objects.sql
Часть 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
Он будет продолжать ошибки (и отображает сообщение об ошибке). Он вернет счет всех найденных объектов.
Часть 2: вызов хранимой процедуры с параметрами:
Вы можете создать цикл 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
Часть 3: Наконец, избавитесь от процедуры:
IF OBJECT_ID('_temp_DropAllDnnObjects') IS NOT NULL
DROP PROCEDURE _temp_DropAllDnnObjects;
GO
Почему бы вместо представления не использовать это для заполнения временной таблицы, которую вы можете использовать?
Это решение, которое я использую в хранимых процедурах
Это лучший способ получить схему динамически и добавить ее в разные таблицы в базе данных, чтобы получить другую информацию динамически
select @sql = 'insert #tables SELECT' '[' '+SCHEMA_NAME (schema_id) +' '.' '+ name +' ']' 'AS SchemaTable FROM sys.tables'
exec (@sql)
конечно #tables - это динамическая таблица в хранимой процедуре