Список невалидных объектов в SQL Server

добавлено: 15 дек 11
понравилось:0
просмотров: 6350
комментов: 1

теги:

Автор: gds

Всем привет.
В обязанности администратора баз данных входит много разных задач, одной из которых является поиск инвалидных (нерабочих) объектов, созданных в бд, и совершение над ними определенных действий.
Эту тему хотелось бы посвятить поиску таких объектов как представления (view), процедуры (procedure) и функции (function), которые в свою очередь делятся на три подвида: SCALAR, TABLE VALUED и INLINE TABLE VALUED.
Все кто знаком оракл, знает, для того что бы получить список инвалидных объектов достаточно выполнить скрипт
-- список объектов 
select owner,object_type,object_name 
from all_objects
where status = 'INVALID'
order by owner,object_type,object_name;

-- объединение по схемам.
select owner,count(*) 
from all_objects
where status = 'INVALID'
group by owner;


К сожалению, в SQL Server получить список таких объектов простым селектом не получится. Я перерыл много страничек интернета в поисках решения данной проблемы, но не нашел универсального решения. Самые распространенные заявления были: sp_refreshview, но это только для представлений и перекомпиляция объектов путем получения тела объекта и изменения конструкции create на alter. Но и тут тоже не все так сладко. Дело в том что при компиляции проверку проходят только представления и функции возвращающие таблицу, т.е. если объекта не существуют они не компилируются. Например:
create view dbo.v_Lecture as
select l.id,l.LectName from dbo.Lecture_1 l
GO

----------------
Msg 208, Level 16, State 1, Procedure v_Lecture, Line 2
Недопустимое имя объекта "dbo.Lecture_1".

или
create function dbo.ftv_Lecture(@id uniqueidentifier)
returns table as 
return (select l.id, l.LectName from dbo.Lecture_1 l where l.id = @id)
GO

Msg 208, Level 16, State 1, Procedure ftv_Lecture, Line 3
Недопустимое имя объекта "dbo.Lecture_1".

остальные объекты компилируются и выдают ошибку только во время выполнения.

Самое лучшее, что мне пришло в голову, да наверное и единственное это строить план запросов этих объектов. Так как если процедура, функция или представления не может выполниться , значит и сервер не может построить план запросов, забегая в перед скажу что и тут есть свои ньюансы.

Итак, была написана вот такая CLR функция, которая в случае успеха возвращала NULL или текст ошибки в случае невозможности построить план, текст ошибки.
+
+ GetInvalidObjectInfo

using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Security.Principal;

public class Compilator
{
    [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString GetInvalidObjectInfo(int object_id)
    {
        string ObjectName = null;
        string ObjectType = null;
        const string SetShowPlanOn = "SET SHOWPLAN_XML ON";
        string CheckSQL = "";
        
        SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();

        using (SqlConnection sql_conn = new SqlConnection("context connection=true"))
        {
            SqlCommand sql_cmd = sql_conn.CreateCommand();
            sql_cmd.CommandText = "select serverproperty('ComputerNamePhysicalNetBIOS') as ServerName, " +
                "serverproperty('InstanceName') as InstanceName, DB_NAME() as DBName ";
            try
            {
                sql_conn.Open();
                using (SqlDataReader dr = sql_cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (dr.Read())
                    {
                        if (dr["InstanceName"] == DBNull.Value)
                            sb.DataSource = Convert.ToString(dr["ServerName"]);
                        else
                            sb.DataSource = string.Format(@"{0}\{1}", Convert.ToString(dr["ServerName"]), Convert.ToString(dr["InstanceName"]));
                        sb.InitialCatalog = Convert.ToString(dr["DBName"]);
                        sb.IntegratedSecurity = true;                        
                    }
                    dr.Close();
                }

            }
            catch (Exception ex)
            {
                return ex.Message.ToString();
            }
        }
        
        using (SqlConnection sql_con = new SqlConnection(sb.ToString()))
        {
            sql_con.Open();
            using (SqlCommand sql_cmd = sql_con.CreateCommand())
            {
                sql_cmd.CommandText = "select quotename(schema_name(schema_id))+'.'+quotename([name]) as fullname, " +
                "rtrim([type]) as [type] from sys.all_objects where object_id = @oid";
                sql_cmd.Parameters.Add("@oid", SqlDbType.Int).Value = object_id;
                try
                {
                    using (SqlDataReader dr = sql_cmd.ExecuteReader())
                    {
                        if (dr.Read())
                        {
                            ObjectName = Convert.ToString(dr[0]);
                            ObjectType = Convert.ToString(dr[1]);
                        }
                        else
                            return string.Format("Not found this object_id: {0}",object_id.ToString());
                    }
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }

            switch (ObjectType)
            {
                case "V":
                    CheckSQL = "select * from " + ObjectName;
                    break;
                case "P":
                    CheckSQL = ObjectName;
                    break;
                case "FN":
                    CheckSQL = ObjectName;
                    break;
                case "IF":
                case "TF":
                    using (SqlCommand sql_cmd = new SqlCommand("declare @s varchar(255) set @s = '' " +
                        "select @s = @s+', '+ case type_name(p.user_type_id) " +
                        "when 'uniqueidentifier' then '''00000000-0000-0000-0000-000000000000''' " +
                        "when 'int' then '0' " +
                        "when 'varchar' then '''varchar''' " +
                        "when 'datetime' then '''19000101''' " +
                        "when 'sysname' then '''sysname''' " +
                        "when 'image' then master.sys.fn_varbintohexstr( CAST( 123456 AS BINARY(4))) " +
                        "when 'xml' then '''<root/>''' " +
                        "when 'sql_variant' then '''sql_variant''' " +
                        "when 'money' then '0' " +
                        "when 'decimal' then '0' " +
                        "when 'timestamp' then master.sys.fn_varbintohexstr( @@DBTS) " +
                        "when 'varbinary' then master.sys.fn_varbintohexstr( CAST( 123456 AS BINARY(4))) " +
                        "when 'text' then master.sys.fn_varbintohexstr( CAST( 123456 AS BINARY(4))) " +
                        "when 'smallint' then '0' " +
                        "when 'binary' then master.sys.fn_varbintohexstr( CAST( 123456 AS BINARY(4))) " +
                        "when 'numeric' then '0' " +
                        "when 'tinyint' then '0' " +
                        "when 'nchar' then '''nchar''' " +
                        "when 'float' then '0' " +
                        "when 'char' then '''char''' " +
                        "when 'real' then '0' " +
                        "when 'bigint' then '0' " +
                        "when 'ntext' then master.sys.fn_varbintohexstr( CAST( 123456 AS BINARY(4))) " +
                        "when 'nvarchar' then 'N''nvarchar''' " +
                        "when 'bit' then '0'	end " +
                        "from sys.parameters p where object_id = @oid " +
                        "and default_value is null " +
                        "order by p.parameter_id " +
                        "select @s = case when len(@s)>0 then substring(@s, 3, len(@s)-2) else @s end " +
                        "select @s", sql_con))
                    {
                        sql_cmd.Parameters.Add("@oid", SqlDbType.Int).Value = object_id;

                        using (SqlDataReader dr = sql_cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (dr.Read())
                            {
                                CheckSQL = string.Format("select * from {0}({1})", ObjectName, Convert.ToString(dr[0]));
                            }
                            else
                                CheckSQL = string.Format("select * from {0}()", ObjectName);
                        }
                    }
                    break;

                default:
                    return "Not support type('" + ObjectType + "')";

            }

            using (SqlCommand sql_cmd = sql_con.CreateCommand())
            {
                sql_cmd.CommandTimeout = 3600;
                try
                {
                    if (ObjectType != "TF")
                    {
                        sql_cmd.CommandText = SetShowPlanOn;
                        sql_cmd.ExecuteNonQuery();
                    }

                    sql_cmd.CommandText = CheckSQL;
                    sql_cmd.ExecuteNonQuery();
                    return SqlString.Null;
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
        } // end using SqlConnection
    } // end function
}


Для использования данной функции необходимо всключить использование CLR на экземпляре сервера
sp_configure 'clr enabled',1
GO
reconfigure with override
GO
alter database [db_name] set trustworthy on
GO

после чего нужно зарегистрировать сборку и создать функцию
Create assembly Compilator
from 'c:\dll\Compilator.dll'
WITH PERMISSION_SET = external_access;
GO
Create function dbo.GetInvalidObjectInfo(@object_id int)
returns nvarchar(max)
AS EXTERNAL NAME Compilator.Compilator.GetInvalidObjectInfo
GO

Для поиска инвалидных объектов я использую вот такой вот скрипт
select 
	 cast(schemaname as varchar(32)) [schema],
	 cast (name  as varchar(64)) name,
	 cast(type as varchar(12)) as [type],
	 err
	 ,'alter schema ['+schemaname+'_old] transfer '+quotename(schemaname)+'.'+quotename(name) as script -- скрипт для перемещение объекта в схему_old
from 
	 (select schema_name(schema_id) as schemaname,name,type,dbo.GetInvalidObjectInfo(object_id) as err 
		  from sys.all_objects
		  where	 [type] in ('V','P','FN','TF','IF') -- Типы объектов для сканированя
					 and name not like 'MSmerge_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
					 and name not like 'sp_MSsync_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
					 and name not like 'sp_MScft_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
					 and schema_name(schema_id) not in ('sys') -- Исключаем схему sys
	 )t 
where 
	 t.err is not null and -- показать объекты с ошибкой компиляции
	 t.err not like 'Invalid object name ''#%' and -- исключить объекты используемые временные таблицы
	 schemaname not like '%old' -- исключить схемы _old
order by 1,2
GO

Немного пройдемся по запросу получения инвалидных объектов.
1. schema_name(schema_id) not in ('sys') -- Исключаем схему sys. Я думаю это понятно.
2.
name not like 'MSmerge_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
name not like 'sp_MSsync_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
name not like 'sp_MScft_%' collate Cyrillic_General_CS_AS -- Исключение объектов репликации (регистр учитывается)
дело в том что эти процедуры созданные репликацией и мы их трогать не будем.
3. schemaname not like '%old' -- исключить схемы _old. Я не удаляю инвалидные объекты, а помещаю их в схему _old. Ниже опишу зачем.
4. t.err is not null and -- показать объекты с ошибкой компиляции. В случае ошибки плана запросов процедура выдает текст ошибки.
5. t.err not like 'Invalid object name ''#%' and -- исключить объекты используемые временные таблицы. Вот на этом я хотел бы остановиться поподробнее. Дело в том что если временная таблица создается путем
select id,name,value into #tmpTable from RealTable

То на момент выполенения этой инструкции таблицы не существует и оптимизатор не знает о существовании данной таблицы и не может построить нормальный план запросов, но инструкция работает и процедура тоже. Выйти из этой ситуации можно следующим образом
if object_id('tempdb..#tmpTable') is not null
 drop table #tmpTable
create table #tmpTable(
...
)
insert  into #tmpTable
select id,name,value from RealTable

При таком раскладе все будет работать и план строиться правильный будет.
6. ,'alter schema ['+schemaname+'_old] transfer '+quotename(schemaname)+'.'+quotename(name) as script -- скрипт для перемещение объекта в схему_old. Я никогда сразу не убиваю эти объекты. Я их переношу в другую схему
alter schema [dbo_old] transfer [dbo].[sp_Lecture]

после чего, если она необходима, её исправляют и переношу обратно, а если она не нужна я её удаляю.

Ну вроде бы все что хотел написать - написал. Если будут замечания или пожелание пишите...

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии