Tips SQL Server

1. Consultar y resetear el contador de un campo Identity.

Consulta: 

SELECT IDENT_CURRENT('nombre_tabla') as IdentCurrent;

Reseteo:

DBCC CHECKIDENT ('nombre_tabla', RESEED, numero)

2. Identificar qué procedimientos o funciones están afectando un campo específico de una tabla.

DECLARE @ColumnName NVARCHAR(128) = 'NombreCampo'

DECLARE @TableName NVARCHAR(128) = 'NombreTabla'

SELECT DISTINCT

    OBJECT_NAME(sm.object_id) AS ObjectName,

    o.type_desc AS ObjectType,

    sm.definition AS Definition

FROM

    sys.sql_modules AS sm

    INNER JOIN sys.objects AS o ON sm.object_id = o.object_id

WHERE

    sm.definition LIKE '%' + @ColumnName + '%'

    AND sm.definition LIKE '%' + @TableName + '%'

ORDER BY

    ObjectName;

3. Identificar que objetos están relacionados con una tabla específica.

DECLARE @TableName NVARCHAR(128) = 'NombreTabla'

SELECT 

    referencing_object_name = OBJECT_NAME(d.referencing_id),

    referencing_object_type_desc = o.type_desc

FROM 

    sys.sql_expression_dependencies AS d

    INNER JOIN sys.objects AS o ON d.referencing_id = o.object_id

WHERE 

    referenced_entity_name = @TableName;