En esta publicación del blog, discutiremos algunas consultas y scripts útiles que se pueden utilizar en el trabajo diario en SQL Server. Estas consultas y scripts pueden ayudarlo a recopilar información sobre sus bases de datos, optimizar el rendimiento y solucionar problemas.
Consulta 1: Listar bases de datos con información de tamaño
Esta consulta proporciona información sobre el tamaño de las bases de datos, incluido el tamaño del archivo de datos y el tamaño del archivo de registro.
SELECT
DB.name,
SUM(CASE WHEN [type] = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS TamañoArchivoDatosMB,
SUM(CASE WHEN [type] = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS TamañoArchivoRegistroMB
FROM
sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- Excluir instantáneas
GROUP BY DB.name
ORDER BY TamañoArchivoDatosMB DESCConsulta 2: Listar objetos con información de espacio
Esta consulta proporciona información sobre los objetos en la base de datos, incluido el espacio total utilizado, el espacio utilizado por la tabla, el espacio utilizado por el índice no agrupado y el recuento de filas.
SELECT
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id) AS [Nombre],
CONVERT(decimal(18,2),SUM(reserved_page_count) * 8/1024.0) AS EspacioTotalUtilizadoMB,
CONVERT(decimal(18,2),SUM(CASE WHEN index_id < 2 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS EspacioUtilizadoTablaMB,
CONVERT(decimal(18,2),SUM(CASE WHEN index_id > 1 THEN reserved_page_count ELSE 0 END ) * 8/1024.0) AS EspacioUtilizadoIndiceNoAgrupadoMB,
MAX(row_count) AS RecuentoFilas
FROM
sys.dm_db_partition_stats AS p
INNER JOIN sys.all_objects AS o ON p.object_id = o.object_id
WHERE
o.is_ms_shipped = 0
GROUP BY
SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(p.object_id)
ORDER BY
EspacioTotalUtilizadoMB DESCConsulta 3: Encontrar el tamaño promedio de las filas en las tablas
Esta consulta calcula el tamaño promedio de las filas en las tablas, incluido el nombre de la tabla, la suma del recuento de registros, el tamaño en MB, el tamaño promedio de registro en bytes, el tamaño máximo de registro en bytes, la fragmentación promedio en porcentaje y el espacio de página promedio utilizado en porcentaje.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
CAST(OBJECT_NAME(ps.OBJECT_ID)+'.'+ISNULL(i.[Name],'montón') AS VARCHAR(60)) AS NombreTablaIndice,
SUM(ps.record_count) AS SumaRecuentoRegistros,
CAST(((SUM(ps.page_count) * 8192) / 1000000.00) AS NUMERIC(9,2)) AS TamañoMB,
AVG(ps.max_record_size_in_bytes) AS TamañoRegistroPromedioEnBytes,
MAX(ps.max_record_size_in_bytes) AS TamañoRegistroMaximoEnBytes,
CAST(AVG(avg_fragmentation_in_percent) AS NUMERIC(6,1)) AS FragmentacionPromedioEnPorcentaje,
CAST(AVG(ps.avg_page_space_used_in_percent) AS NUMERIC(6,1)) AS EspacioPaginaPromedioUtilizadoEnPorcentaje
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps --Debe usar DETAILED
LEFT JOIN [sys].indexes AS i ON i.OBJECT_ID = ps.OBJECT_ID AND i.index_id = ps.index_id
--WHERE OBJECT_NAME(ps.OBJECT_ID) IN ('Empleado') --Use filtrado aquí si desea resultados solo para tablas específicas, se ejecuta más rápido en bases de datos grandes
GROUP BYOBJECT_NAME(ps.OBJECT_ID), i.[Name]
ORDER BYOBJECT_NAME(ps.OBJECT_ID), i.[Name];Consulta 4: Obtener información de fragmentación para las tablas
Esta consulta proporciona información de fragmentación para las tablas, incluido el nombre del esquema, el nombre de la tabla, el nombre del índice, el ID del índice, el tipo de índice, la fragmentación externa, el número de páginas, el factor de llenado y el porcentaje de llenado de la página.
SELECT
ss.[Name] [Esquema],
OBJECT_NAME(ddips.OBJECT_ID) [NombreTabla],
ISNULL(si.[Name],'') [NombreIndice],
si.Index_id,
si.[Type_desc],
ISNULL(ddips.avg_fragmentation_in_percent,0) [FragmentacionExterna],
ddips.page_count [Paginas],
si.Fill_factor,
ISNULL(ddips.avg_page_space_used_in_percent,0) [PorcentajeLlenadoPagina]
FROM [sys].dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ddips /*DETAILED ofrece más, pero consume más CPU*/ JOIN [sys].indexes si ON ddips.index_id = si.index_id AND ddips.OBJECT_ID = si.OBJECT_ID
JOIN [sys].tables st ON ddips.OBJECT_ID = st.OBJECT_ID
JOIN [sys].schemas ss ON st.SCHEMA_ID = ss.SCHEMA_ID
WHERE ddips.index_level = 0 AND si.index_id > 0 AND st.[Type] = N'U' /* nivel de hoja, no montones, definidos por el usuario */GROUP BYss.[Name], ddips.OBJECT_ID, si.[Name], si.index_id, si.type_desc, avg_fragmentation_in_percent, ddips.page_count,
avg_page_space_used_in_percent,si.fill_factor
ORDER BY ddips.page_count DESC;Consulta 5: Recomendación para índices potencialmente faltantes
Esta consulta proporciona recomendaciones para índices potencialmente faltantes, incluyendo la medida de mejora, la declaración de creación de índice y información adicional sobre el índice.
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS medida_mejora,
'CREATE INDEX [IX_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.STATEMENT, 1), 32) + ']'
+ ' ON ' + mid.STATEMENT
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS declaracion_creacion_indice,
migs.*, mid.database_id, mid.[object_id]
FROM [sys].dm_db_missing_index_groups mig
INNER JOIN [sys].dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN [sys].dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;Consulta 6: Encontrar los 50 principales índices no utilizados en una base de datos
Esta consulta identifica los 50 principales índices no utilizados en una base de datos, incluido el nombre del objeto, el nombre del índice, el ID del índice, las búsquedas de usuario, las exploraciones de usuario, las búsquedas de usuario, las actualizaciones de usuario, las filas de la tabla y la declaración de eliminación.
SELECT TOP 50
o.[Name] AS [NombreObjeto]
, i.[Name] AS NombreIndice
, i.index_id AS IDIndice
, dm_ius.user_seeks AS BúsquedasUsuario
, dm_ius.user_scans AS ExploracionesUsuario
, dm_ius.user_lookups AS BúsquedasUsuario
, dm_ius.user_updates AS ActualizacionesUsuario
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.[Name])
+ ' ON ' + QUOTENAME(s.[Name]) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'DeclaraciónEliminación'
FROM [sys].dm_db_index_usage_stats dm_ius
INNER JOIN [sys].indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN [sys].objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN (SELECT SUM(p.ROWS) TableRows, p.index_id, p.OBJECT_ID
FROM [sys].partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC;Consulta 7: Tablas con desencadenadores INSTEAD OF
Esta consulta enumera las tablas que tienen desencadenadores INSTEAD OF.
SELECT
s.[Name] + N'.' + t.[Name] [Tabla]
FROM [sys].tables t
INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
( SELECT * FROM [sys].triggers tr
WHERE tr.parent_id = t.[object_id] AND tr.is_instead_of_trigger = 1
);Consulta 8: Tablas que no tienen clave primaria
Esta consulta enumera las tablas que no tienen una clave primaria.
SELECT s.[name] + N'.' + t.[name] [Tabla]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
( SELECT * FROM sys.key_constraints kc
WHERE kc.[type] = N'PK' AND kc.parent_object_id = t.[object_id]
);Consulta 9: Encontrar objetos que utilizan compresión
Esta consulta enumera los objetos en la base de datos que utilizan compresión, incluido el nombre del esquema, el nombre del objeto, el comando de descompresión, la tabla tiene formato de almacenamiento VarDecimal, las filas, el tipo de compresión y el ID de índice en la tabla.
SELECT
SCHEMA_NAME([sys].objects.SCHEMA_ID) AS [NombreEsquema]
,OBJECT_NAME([sys].objects.OBJECT_ID) AS [NombreObjeto]
,'ALTER INDEX ALL ON '+SCHEMA_NAME([sys].objects.SCHEMA_ID)+'.'
+OBJECT_NAME([sys].objects.OBJECT_ID)+' REBUILD WITH (DATA_COMPRESSION = None);' [ComandoDescompresión]
,(SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME([sys].objects.OBJECT_ID)),
'TableHasVarDecimalStorageFormat') ) AS [TablaTieneFormatoAlmacenamientoVarDecimal]
,[Rows]
,[data_compression_desc] [TipoCompresión]
,[Index_id] AS [IDIndiceEnTabla]
FROM [sys].partitions
INNER JOIN [sys].objects ON [sys].partitions.OBJECT_ID = [sys].objects.OBJECT_ID
WHERE [data_compression] > 0 AND SCHEMA_NAME([sys].objects.SCHEMA_ID) <> 'SYS'
ORDER BY [NombreEsquema], [NombreObjeto];Script 10: Recompilar todos los objetos programables en una base de datos
Este script vuelve a compilar todos los objetos programables en una base de datos, incluidos los procedimientos almacenados, las funciones y los desencadenadores.
DECLARE sps CURSOR FOR
SELECT ROUTINE_NAME
FROM [INFORMATION_SCHEMA].routines
WHERE ROUTINE_TYPE = 'PROCEDURE';
OPEN sps;
DECLARE @RoutineName VARCHAR(128);
DECLARE @SQLString NVARCHAR(2048);
FETCH NEXT FROM sps
INTO @RoutineName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'EXECUTE sp_recompile '+@RoutineName;
PRINT @SQLString;
EXECUTE sp_ExecuteSQL @SQLString;
FETCH NEXT FROM sps
INTO @RoutineName;
END;
CLOSE sps;
DEALLOCATE sps;Script 11: Actualizar todas las vistas en una base de datos
Este script actualiza todas las vistas en una base de datos.
DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT
DISTINCT s.[Name] + '.' + o.[Name] AS NombreVista
FROM [sys].objects o JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHEREo.[type] = 'V'
AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;
OPEN viewlist;
FETCH NEXT FROM viewlist
INTO @ActualView;
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ActualView
BEGIN TRY
EXECUTE sp_refreshview @ActualView;
END TRY
BEGIN CATCH
PRINT 'La vista '+@ActualView+' no se puede actualizar.';
END CATCH;
FETCH NEXT FROM viewlist
INTO @ActualView;
END;
CLOSE viewlist;
DEALLOCATE viewlist;Consulta 12: Encontrar todas las restricciones que deben confiarse
Esta consulta enumera todas las restricciones que deben confiarse, incluido el nombre del esquema, el nombre de la tabla, el nombre de la restricción, el estado de confianza, el tipo de restricción y el comando SQL para confiar en la restricción.
SELECT
SCHEMA_NAME(s.[schema_id]) [Esquema],
OBJECT_NAME(fk.parent_object_id) [NombreTabla],
fk.[name] [Restricción],
CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Sí' END [Confiado],
fk.[Type_desc],
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(fk.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+fk.name) [ComandoSQLParaConfiarEnLaRestricción]
FROM sys.foreign_keys fk
INNER JOIN sys.objects o ON fk.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE fk.is_not_trusted = 1 AND fk.is_not_for_replication = 0
UNION ALL
SELECT
SCHEMA_NAME(s.[schema_id]) [Esquema],
OBJECT_NAME(cc.parent_object_id) [NombreTabla],
cc.[name] [Restricción],
CASE is_not_trusted WHEN 1 THEN 'No' ELSE 'Sí' END [Confiado],
cc.[type_desc],
('ALTER TABLE '+QUOTENAME(SCHEMA_NAME(s.[schema_id]))+'.'+
QUOTENAME(OBJECT_NAME(cc.parent_object_id))+
' WITH CHECK CHECK CONSTRAINT '+cc.name) [ComandoSQLParaConfiarEnLaRestricción]
FROM sys.check_constraints cc
INNER JOIN sys.objects o ON cc.parent_object_id = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
WHERE cc.is_not_trusted = 1 AND cc.is_not_for_replication = 0 AND cc.is_disabled = 0;Script 13: Finalizar todos los procesos de usuario para una base de datos
Este script finaliza todos los procesos de usuario para una base de datos. Úselo con precaución, ya que puede terminar las conexiones activas.
IF EXISTS (SELECT * FROM [sys].objects
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[sp_kill_sql_db_sys_processes]')
AND [Type] IN (N'P', N'PC')
)
DROP PROCEDURE [dbo].[sp_kill_sql_db_sys_processes];
GO
CREATE PROCEDURE [dbo].[sp_kill_sql_db_sys_processes]
@dbName NVARCHAR(100)
AS
BEGIN
DECLARE @spid INT;
DECLARE @sqlString NVARCHAR(100);
DECLARE conn_cursor CURSOR FOR
SELECT [SPID] FROM [master].[dbo].sysprocesses
WHERE [DbId] = DB_ID(@dbName) AND [SPID] <> @@spid;
OPEN conn_cursor;
FETCH NEXT FROM conn_cursor INTO @spid;
WHILE @@fetch_status=0
BEGIN
SET @sqlString = 'KILL '+CAST(@spid AS NVARCHAR(10));
PRINT @sqlString;
EXECUTE sp_executeSql @sqlString;
FETCH NEXT FROM conn_cursor INTO @spid;
END;
CLOSE conn_cursor;
DEALLOCATE conn_cursor;
END;
GO
Exec sp_kill_sql_db_sys_processes @dbName='nombre_de_su_base_de_datos';Script 14: Listar todas las bibliotecas en una base de datos
Este script enumera todas las bibliotecas en una base de datos, incluido el nombre del esquema, el nombre del objeto, el conjunto de permisos, la clase de la biblioteca y el método de la biblioteca.
SELECT so.[Name], so.[type], SCHEMA_NAME(so.SCHEMA_ID) AS [Esquema],
asmbly.[Name], asmbly.permission_set_desc, am.assembly_class,
am.assembly_method
FROM [sys].assembly_modules am
INNER JOIN [sys].assemblies asmbly
ON asmbly.assembly_id = am.assembly_id AND asmbly.[Name] NOT LIKE 'Microsoft%'
INNER JOIN [sys].objects so
ON so.OBJECT_ID = am.OBJECT_ID
UNION
SELECT at.[Name], 'TYPE' AS [type], SCHEMA_NAME(AT.SCHEMA_ID) AS [Esquema],
asmbly.[Name], asmbly.permission_set_desc, AT.assembly_class,
NULL AS [assembly_method]
FROM [sys].assembly_types at
INNER JOIN [sys].assemblies asmbly
ON asmbly.assembly_id = at.assembly_id
AND asmbly.[Name] NOT LIKE 'Microsoft%'
ORDER BY 4, 2, 1;Consulta 15: Verificar si su declaración T-SQL dinámica es válida
Esta consulta verifica si su declaración T-SQL dinámica es válida. Devuelve 1 si la declaración es válida y 0 si no lo es.
CREATE PROCEDURE IsValidSQL (@sql VARCHAR(MAX)) AS
BEGIN
BEGIN TRY
SET @sql = 'SET PARSEONLY ON;'+@sql;
EXECUTE(@sql);
END TRY
BEGIN CATCH
RETURN(0); --Fallo
END CATCH;
RETURN(1); --Éxito
END; -- IsValidSQL
--Prueba:
--Fallo
DECLARE @retval INT;
EXECUTE @retval = IsValidSQL 'SELECT IIF(val, 0, 1) FROM T'; --T no existe
SELECT @retval;
GO
--Éxito
CREATE TABLE #T(id INT IDENTITY(1,1),val VARCHAR(100));
DECLARE @retval INT;
EXECUTE @retval = IsValidSQL 'SELECT val FROM from #T'; --#T existe
SELECT @retval;Consulta 16: Todas las estadísticas creadas por el usuario
Esta consulta enumera todas las estadísticas creadas por el usuario en la base de datos, incluido el nombre de la tabla, el nombre de la estadística, el nombre de la columna, el tipo de datos y la longitud de la columna.
SELECT
st.[Name] [NombreTabla], ss.[Name] NombreEstadistica,
sc.[Name] AS [NombreColumna],
t.[Name] AS TipoDato,
CASE
WHEN sc.max_length = -1 THEN 'varchar(max), nvarchar(max), varbinary(max) o xml'
ELSE CAST(sc.max_length AS VARCHAR(10))
END AS LongitudColumna
FROM [sys].stats ss
JOIN [sys].tables st ON ss.OBJECT_ID=st.OBJECT_ID
JOIN [sys].stats_columns ssc ON ss.stats_id=ssc.stats_id AND st.OBJECT_ID=ssc.OBJECT_ID
JOIN [sys].columns sc ON ssc.column_id=sc.column_id AND st.OBJECT_ID=sc.OBJECT_ID
JOIN [sys].types t ON sc.system_type_id=t.system_type_id
WHERE ss.user_created = 1
ORDER BY t.[Name], st.[Name];Consulta 17: Tablas con más de 30 columnas (tablas anchas)
Esta consulta enumera las tablas con más de 30 columnas, también conocidas como tablas anchas.
DECLARE @threshold INT;
SET @threshold = 30;
;WITH cte AS
(
SELECT [object_id], COUNT(*) [Columnas]
FROM sys.columns
GROUP BY [object_id]
HAVING COUNT(*) > @threshold
)
SELECT
s.[name] + N'.' + t.[name] [Tabla],
c.[Columnas]
FROM cte c
INNER JOIN sys.tables t ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
ORDER BY c.[column count] DESC;Consulta 18: Tablas con más de 5 índices
Esta consulta enumera las tablas con más de 5 índices.
DECLARE @threshold INT;
SET @threshold = 5;
SELECT
[Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
INNER JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].indexes i
WHERE i.[object_id] = t.[object_id]
GROUP BY i.[object_id]
HAVING COUNT(*) > @threshold
);Consulta 19: Tablas sin un índice agrupado (montón)
Esta consulta enumera las tablas sin un índice agrupado, también conocidas como tablas de montón.
SELECT [Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
INNER JOIN [sys].schemas s
ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS
(
SELECT 1 FROM [sys].indexes i
WHERE i.[object_id] = t.[object_id]
AND i.index_id = 1
);Consulta 20: Tablas con sus filas
Esta consulta proporciona el número de filas para cada tabla en la base de datos.
SELECT s.[Name] + N'.' + t.[Name] [Tabla], p.[Rows]
FROM [sys].tables t
JOIN [sys].schemas s ON s.SCHEMA_ID = t.SCHEMA_ID
JOIN [sys].partitions p ON p.OBJECT_ID = t.OBJECT_ID AND p.index_id IN (0,1); --montón o índice agrupadoConsulta 21: Tablas con columnas XML
Esta consulta enumera las tablas que tienen columnas XML.
SELECT [Tabla] = s.name + N'.' + t.name
FROM sys.tables t
INNER JOIN sys.schemas s
ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM sys.columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id = 241 -- 241 = xml
);Consulta 22: Tablas con al menos una columna LOB (max)
Esta consulta enumera las tablas que tienen al menos una columna LOB (max).
SELECT [Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].columns c
WHERE c.[object_id] = t.[object_id]
AND c.max_length = -1
AND c.system_type_id IN
(
165, -- varbinary
167, -- varchar
231 -- nvarchar
)
);Consulta 23: Tablas con al menos una columna TEXT, NTEXT, IMAGE
Esta consulta enumera las tablas que tienen al menos una columna TEXT, NTEXT o IMAGE.
SELECT [Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id IN
(
34, -- image
35, -- text
99 -- ntext
)
);Consulta 24: Tablas con columnas de identidad
Esta consulta enumera las tablas que tienen columnas de identidad.
SELECT [Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT * FROM [sys].identity_columns i
WHERE i.[object_id] = t.[object_id]
);Consulta 25: Tablas con al menos dos desencadenadores
Esta consulta enumera las tablas que tienen al menos dos desencadenadores.
DECLARE @min_count INT;
SET @min_count = 2;
SELECT [Tabla] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].triggers tr
WHERE tr.parent_id = t.[object_id]
GROUP BY tr.parent_id
HAVING COUNT(*) >= @min_count
);Consulta 26: Orden de dependencia de las tablas
Esta consulta enumera las tablas en orden de dependencia, comenzando con las tablas que no tienen dependencias de clave externa.
;WITH a AS
(
SELECT
0 AS lvl, t.OBJECT_ID AS tblID
FROM [sys].tables t
WHERE t.is_ms_shipped = 0
AND t.OBJECT_ID NOT IN (SELECT f.referenced_object_id
FROM [sys].foreign_keys f)
UNION ALL
SELECT
a.lvl + 1 AS lvl, f.referenced_object_id AS tblId
FROM a
INNER JOIN [sys].foreign_keys f ON a.tblId = f.parent_object_id
AND a.tblID != f.referenced_object_id
)
SELECT
OBJECT_SCHEMA_NAME(tblID) [nombre_esquema], OBJECT_NAME(tblId) [nombre_tabla], a.lvl
FROM a
GROUP BY tblId, a.lvl
ORDER BY MAX(lvl), 1;Consulta 27: Roles del servidor y permisos por inicio de sesión
Esta consulta enumera los roles del servidor y los permisos para cada inicio de sesión, incluido el principal del servidor, el tipo de inicio de sesión, el estado deshabilitado del usuario, la fecha de creación, la fecha de modificación, la base de datos predeterminada, el idioma predeterminado, la lista de roles del servidor y los permisos del servidor.
SELECT
sp.[Name] AS PrincipalServidor, sp.[type_desc] AS TipoInicioSesion,
CASE sp.is_disabled
WHEN 0 THEN 'No'
WHEN 1 THEN 'Sí'
END AS UsuarioDeshabilitado,
sp.create_date AS FechaCreacion, sp.modify_date AS FechaModificacion, sp.default_database_name AS BaseDatosPredeterminada, sp.default_language_name AS IdiomaPredeterminado,
ISNULL(STUFF((
SELECT ',' + CASE ssp22.[Name] WHEN 'sysadmin' THEN ssp22.[Name] + ' "Privilegios completos"' ELSE ssp22.[Name] END
FROM [sys].server_principals ssp2
INNER JOIN [sys].server_role_members ssrm2 ON ssp2.principal_id = ssrm2.member_principal_id
INNER JOIN [sys].server_principals ssp22 ON ssrm2.role_principal_id = ssp22.principal_id
WHERE ssp2.principal_id = sp.principal_id
ORDER BY ssp2.[Name]
FOR XML PATH (N''), TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No tiene roles') AS ListaRolesServidor,
ISNULL(STUFF((
SELECT ';' + ' Permiso [' + sspm3.[permission_name] + '] es [' + CASE WHEN sspm3.[state_desc] = 'GRANT' THEN 'Concedido]' WHEN sspm3.[state_desc] = 'DENY' THEN 'Denegado]' END AS Permisos
FROM [sys].server_principals ssp3
INNER JOIN [sys].server_permissions sspm3 ON ssp3.principal_id = sspm3.[grantee_principal_id]
WHERE sspm3.[class] = 100 AND sspm3.[grantee_principal_id] = sp.principal_id
FOR XML PATH (N''), TYPE
).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No tiene permisos de servidor') + ' en Server::' + @@ServerName + '' AS Permisos
FROM [sys].server_principals sp
WHERE sp.[Type] IN ('S', 'G', 'U') AND sp.[Name] NOT LIKE '##%##'
ORDER BY PrincipalServidor;Script 28: Realinear la identidad de las tablas
Este script realinea la identidad de las tablas en caso de que la alineación de la identidad sea importante para su lógica empresarial. Úselo con precaución, ya que puede causar pérdida de identidad en ciertos casos.
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX)
DECLARE Cur CURSOR
LOCAL FAST_FORWARD
FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(int, c.last_value)
FROM sys.identity_columns AS c
INNER JOIN sys.tables AS t
ON c.object_id = t.object_id
WHERE c.last_value > c.seed_value
OPEN Cur
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CMD = N'
SELECT @pResult = N''DBCC CHECKIDENT(''''' + @CurrTable + N''''', RESEED, '' + CONVERT(nvarchar(max), MAX(' + QUOTENAME(@CurrCol) + N')) + N'') -- ' + CONVERT(nvarchar(max), @LastValue) + N'''
FROM ' + @CurrTable + N'
HAVING MAX(' + QUOTENAME(@CurrCol) + N') <> @LastValue'
EXEC sp_executesql @CMD, N'@pResult NVARCHAR(MAX) OUTPUT, @LastValue BIGINT', @Result OUTPUT, @LastValue;
IF @Result IS NOT NULL
PRINT @Result;
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue
END
CLOSE Cur
DEALLOCATE Cur;Script 29: Script para crear un punto de recuperación de base de datos
Este script crea un punto de recuperación de base de datos. Úselo con precaución, ya que los puntos de recuperación pueden ralentizar las actividades de la base de datos.
CREATE DATABASE SuBaseDeDatos_PuntoRecuperacion ON
( NAME = SuBaseDeDatos, FILENAME = 'C:\SuBaseDeDatos_PuntoRecuperacion.ss' )
AS SNAPSHOT OF SuBaseDeDatos;Script 30: Ejecutar sp_WhoIsActive
Este script ejecuta el procedimiento almacenado sp_WhoIsActive, que proporciona información detallada sobre la actividad actual de SQL Server, incluidas las consultas activas, los procesos de bloqueo y el uso de recursos.
EXEC sp_WhoIsActive;Script 31: Scripts de Bernt Ozar
Bernt Ozar proporciona una colección de scripts útiles para SQL Server, incluidos scripts para ajuste de rendimiento, mantenimiento de índices y verificaciones de salud de la base de datos. Puede encontrar estos scripts en su sitio web.
Estas consultas y scripts pueden ser una adición valiosa a su conjunto de herramientas de SQL Server. Pueden ayudarlo a recopilar información, optimizar el rendimiento y solucionar problemas en su entorno de SQL Server. Siéntase libre de usarlos y modificarlos según sus necesidades específicas.
¡Gracias por leer!</