El SQL es un gran conocido entre todos los desarrolladores, pero no todos saben extraer su verdadero jugo. En este artículo te mostramos 10 snippets de SQL que puedes poner en práctica en tus propios proyectos sin riesgo alguno. Los hay de todo tipo, desde uno para listar aquellas queries más lentas y pesadas, hasta otro que muestra las dependencias entre tablas.
Listado de queries más pesadas
SELECT DISTINCT TOP 10 t.TEXT AS QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC GO
Crear un log DDL de la base de datos
Creamos primero el log:
CREATE DATABASE [DDL_ChangeLog] GO use ddl_changelog; CREATE TABLE DDLChangeLog ( DDLChangeLogID [int] IDENTITY(1,1) NOT NULL, eventtype nvarchar(100), objectname nvarchar(100), objecttype nvarchar(100), databasename nvarchar(100), schemaname nvarchar(100), SystemUser nvarchar(255), AppName nvarchar(255), HostName nvarchar(255), tsql nvarchar(MAX), createddate datetime, CONSTRAINT [PK_DDL_Changelog] PRIMARY KEY CLUSTERED (DDLChangeLogID ASC))
Y después creamos el trigger para registrar cualquier cambio en el DDL
CREATE TRIGGER [trgLogDDLEvent] ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML SET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_STATISTICS' INSERT INTO ddl_changelog..DDLChangeLog (EventType, ObjectName, ObjectType, DatabaseName, SchemaName, SystemUser, AppName, HostName, tsql , createddate) VALUES ( @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'), @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'), system_user , app_name (),host_name(), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') , getdate()) ; GO ENABLE TRIGGER [trgLogDDLEvent] ON DATABASE GO
Saber qué cambios se han hecho en los procedimientos almacenados en los últimos X días
SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 7 ----Change 7 to any other day value SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,create_date, GETDATE()) < 7
Fragmentar todos los índices de las tablas de una base de datos
SET NOCOUNT ON DECLARE @tablename VARCHAR (128) DECLARE @execstr VARCHAR (255) DECLARE @objectid INT DECLARE @indexid INT DECLARE @frag DECIMAL DECLARE @maxfrag DECIMAL DECLARE @dbname VARCHAR (40) if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FragStats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[FragStats] ( [ObjectName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ObjectId] [int] NULL , [IndexName] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IndexId] [int] NULL , [Lvl] [int] NULL , [CountPages] [int] NULL , [CountRows] [int] NULL , [MinRecSize] [int] NULL , [MaxRecSize] [int] NULL , [AvgRecSize] [int] NULL , [ForRecCount] [int] NULL , [Extents] [int] NULL , [ExtentSwitches] [int] NULL , [AvgFreeBytes] [int] NULL , [AvgPageDensity] [int] NULL , [ScanDensity] [decimal](18, 0) NULL , [BestCount] [int] NULL , [ActualCount] [int] NULL , [LogicalFrag] [decimal](18, 0) NULL , [ExtentFrag] [decimal](18, 0) NULL , [TStamp] [datetime] NULL , [DBName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) -- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Create the table CREATE TABLE #fraglist ( ObjectName CHAR (255), ObjectId INT, IndexName CHAR (255), IndexId INT, Lvl INT, CountPages INT, CountRows INT, MinRecSize INT, MaxRecSize INT, AvgRecSize INT, ForRecCount INT, Extents INT, ExtentSwitches INT, AvgFreeBytes INT, AvgPageDensity INT, ScanDensity DECIMAL, BestCount INT, ActualCount INT, LogicalFrag DECIMAL, ExtentFrag DECIMAL) -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE cv_FETCH_STATUS = 0 BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END -- Close and deallocate the cursor CLOSE tables DEALLOCATE tables insert into [FragStats] select *,getdate(),@dbname from #Fraglist drop table #fraglist go
Listado de sentencias bloqueadas y en espera
select t1.resource_type ,db_name(resource_database_id) as [database] ,t1.resource_associated_entity_id as [blk object] ,t1.request_mode ,t1.request_session_id -- spid of waiter ,(select text from sys.dm_exec_requests as r --- get sql for waiter cross apply sys.dm_exec_sql_text(r.sql_handle) where r.session_id = t1.request_session_id) as waiter_text ,t2.blocking_session_id -- spid of blocker ,(select text from sys.sysprocesses as p --- get sql for blocker cross apply sys.dm_exec_sql_text(p.sql_handle) where p.spid = t2.blocking_session_id) as blocker_text from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address go
Listado de índices poco utilizados en una tabla
declare @dbid int select @dbid = db_id() select objectname=object_name(s.object_id), s.object_id , indexname=i.name, i.index_id , user_seeks, user_scans, user_lookups, user_updates from sys.dm_db_index_usage_stats s, sys.indexes i where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1 and i.object_id = s.object_id and i.index_id = s.index_id order by (user_seeks + user_scans + user_lookups + user_updates) asc
Recompilar todos los procedimientos almacenados de una base de datos
CREATE PROCEDURE dbo.spEXECsp_RECOMPILE AS SET NOCOUNT ON -- 1a - Declaration statements for all variables DECLARE @TableName varchar(128) DECLARE @OwnerName varchar(128) DECLARE @CMD1 varchar(8000) DECLARE @TableListLoop int DECLARE @TableListTable table (UIDTableList int IDENTITY (1,1), OwnerName varchar(128), TableName varchar(128)) -- 2a - Outer loop for populating the database names INSERT INTO @TableListTable(OwnerName, TableName) SELECT TOP 10 u.[Name], o.[Name] FROM dbo.sysobjects o INNER JOIN dbo.sysusers u ON o.uid = u.uid WHERE o.Type = 'U' ORDER BY o.[Name] -- 2b - Determine the highest UIDDatabaseList to loop through the records SELECT @TableListLoop = MAX(UIDTableList) FROM @TableListTable -- 2c - While condition for looping through the database records WHILE @TableListLoop > 0 BEGIN -- 2d - Set the @DatabaseName parameter SELECT @TableName = TableName, @OwnerName = OwnerName FROM @TableListTable WHERE UIDTableList = @TableListLoop -- 3f - String together the final backup command SELECT @CMD1 = 'EXEC sp_recompile ' + '[' + @OwnerName + '.' + @TableName + ']' + char(13) -- 3g - Execute the final string to complete the backups -- SELECT @CMD1 EXEC (@CMD1) -- 2h - Descend through the database list SELECT @TableListLoop = @TableListLoop - 1 END SET NOCOUNT OFF GO
Mostrar la dependencia entre tablas
set nocount on declare @ProcName nvarchar(100) declare @ProcSortOrder int declare @MyCursor CURSOR declare @ProcUser varchar(100) select @ProcUser = 'my user' declare @StoredProcs TABLE ( SortOrder int, ProcedureName varchar(100), ProcedureCode varchar(7500) ) Insert Into @StoredProcs select 0,upper(SysObjects.Name),SysComments.Text from SysObjects,SysComments where SysObjects.type='P' and (SysObjects.Category = 0) and (SysObjects.ID = SysComments.ID) order by SysObjects.Name ASC set nocount off SET @MyCursor = CURSOR FAST_FORWARD FOR select ProcedureName, SortOrder = (select count(*) from @StoredProcs B WHERE (A.ProcedureName <> B.ProcedureName) and (REPLACE(UPPER(B.ProcedureCode),B.ProcedureName,'') LIKE '%' + upper(A.ProcedureName) + '%') ) from @StoredProcs A order by SortOrder Desc OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @ProcName,@ProcSortOrder WHILE cv_FETCH_STATUS = 0 BEGIN PRINT 'if exists (select * from dbo.sysobjects ' PRINT ' where id = object_id(N' + char(39) + '[dbo].[' + @ProcName + ']' + char(39) + ')' PRINT ' and OBJECTPROPERTY(id, N' + char(39) + 'IsProcedure' + char(39) + ') = 1) ' PRINT ' drop procedure ' + @ProcName PRINT ' GO ' PRINT ' SET QUOTED_IDENTIFIER OFF ' PRINT ' GO ' PRINT ' SET ANSI_NULLS OFF ' PRINT ' GO' exec sp_helptext @ProcName PRINT ' GO ' PRINT ' SET QUOTED_IDENTIFIER OFF ' PRINT ' GO ' PRINT ' SET ANSI_NULLS ON ' PRINT ' GO ' PRINT ' GRANT EXECUTE ON [dbo].[' + @ProcName + '] TO [' + @ProcUser + ']' PRINT ' GO ' /* PRINT @ProcName + ' ' + cast(@ProcSortOrder as varchar(20)) */ FETCH NEXT FROM @MyCursor INTO @ProcName,@ProcSortOrder END CLOSE @MyCursor DEALLOCATE @MyCursor
Auditar quién tiene permisos de ejecución por procedimientos almacenados o nombre de usuario
drop table #t2 go select case when issqluser=1 then 'SQL User' when isntuser=1 then 'NT User' when isntgroup=1 then 'NT Group' when isntname=1 then 'NT Group or User' when issqlrole=1 then 'SQL Group/Role' end 'User Type', user_name(pro.uid) 'User/Group Name', --pro.id, --obj.id, obj.name, --pro.uid, --pro.action, case when pro.action = 224 then 'X' else ' ' end as 'EXECUTE' into #t2 from sysprotects pro join sysobjects obj on pro.id=obj.id join sysusers su on pro.uid=su.uid where obj.type='P' and pro.action in (224) go -- --results by group -- select cast([User/Group Name] as char(30)) 'User/Group Name', [User Type], cast([name] as char(30)) 'Proceedure Name', [Execute] from #t2 order by [User/Group Name], [User Type], [name] go -- --results by proc name -- select cast([name] as char(30)) 'Procedure Name', [User Type], cast([User/Group Name] as char(30)) 'User/Group Name', [Execute] from #t2 order by [name], [User Type], [User/Group Name] go
Snippet para reconstruir una tabla
CREATE PROCEDURE sp_ScriptTable ( @TableName SYSNAME, @IncludeConstraints BIT = 1, @IncludeIndexes BIT = 1, @NewTableName SYSNAME = NULL, @UseSystemDataTypes BIT = 0 ) AS BEGIN DECLARE @MainDefinition TABLE ( FieldValue VARCHAR(200) ) DECLARE @DBName SYSNAME DECLARE @ClusteredPK BIT DECLARE @TableSchema NVARCHAR(255) SET @DBName = DB_NAME(DB_ID()) SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName) DECLARE @ShowFields TABLE ( FieldID INT IDENTITY(1,1), DatabaseName VARCHAR(100), TableOwner VARCHAR(100), TableName VARCHAR(100), FieldName VARCHAR(100), ColumnPosition INT, ColumnDefaultValue VARCHAR(100), ColumnDefaultName VARCHAR(100), IsNullable BIT, DataType VARCHAR(100), MaxLength INT, NumericPrecision INT, NumericScale INT, DomainName VARCHAR(100), FieldListingName VARCHAR(110), FieldDefinition CHAR(1), IdentityColumn BIT, IdentitySeed INT, IdentityIncrement INT, IsCharColumn BIT ) DECLARE @HoldingArea TABLE ( FldID SMALLINT IDENTITY(1,1), Flds VARCHAR(4000), FldValue CHAR(1) DEFAULT(0) ) DECLARE @PKObjectID TABLE ( ObjectID INT ) DECLARE @Uniques TABLE ( ObjectID INT ) DECLARE @HoldingAreaValues TABLE ( FldID SMALLINT IDENTITY(1,1), Flds VARCHAR(4000), FldValue CHAR(1) DEFAULT(0) ) DECLARE @Definition TABLE ( DefinitionID SMALLINT IDENTITY(1,1), FieldValue VARCHAR(200) ) INSERT INTO @ShowFields ( DatabaseName, TableOwner, TableName, FieldName, ColumnPosition, ColumnDefaultValue, ColumnDefaultName, IsNullable, DataType, MaxLength, NumericPrecision, NumericScale, DomainName, FieldListingName, FieldDefinition, IdentityColumn, IdentitySeed, IdentityIncrement, IsCharColumn ) SELECT DB_NAME(), TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CAST(ORDINAL_POSITION AS INT), COLUMN_DEFAULT, dobj.name AS ColumnDefaultName, CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, DATA_TYPE, CAST(CHARACTER_MAXIMUM_LENGTH AS INT), CAST(NUMERIC_PRECISION AS INT), CAST(NUMERIC_SCALE AS INT), DOMAIN_NAME, COLUMN_NAME + ',', '' AS FieldDefinition, CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn, CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed, CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement, CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn FROM INFORMATION_SCHEMA.COLUMNS c JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D' WHERE c.TABLE_NAME = @TableName ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION SELECT TOP 1 @TableSchema = TableOwner FROM @ShowFields INSERT INTO @HoldingArea (Flds) VALUES('(') INSERT INTO @Definition(FieldValue) VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END) INSERT INTO @Definition(FieldValue) VALUES('(') INSERT INTO @Definition(FieldValue) SELECT CHAR(10) + FieldName + ' ' + CASE WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END ELSE UPPER(DataType) + CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END + CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END + CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END END + CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END FROM @ShowFields IF @IncludeConstraints = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')' FROM ( SELECT ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name, REVERSE(SUBSTRING(REVERSE(( SELECT cp.name + ',' FROM sys.foreign_key_columns fkc JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id WHERE fkc.constraint_object_id = fk.object_id FOR XML PATH('') )), 2, 8000)) ParentColumns, REVERSE(SUBSTRING(REVERSE(( SELECT cr.name + ',' FROM sys.foreign_key_columns fkc JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id WHERE fkc.constraint_object_id = fk.object_id FOR XML PATH('') )), 2, 8000)) ReferencedColumns FROM sys.foreign_keys fk ) a WHERE ParentObject = @TableName INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints WHERE OBJECT_NAME(parent_object_id) = @TableName INSERT INTO @PKObjectID(ObjectID) SELECT DISTINCT PKObject = cco.object_id FROM sys.key_constraints cco JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 1 AND is_primary_key = 1 INSERT INTO @Uniques(ObjectID) SELECT DISTINCT PKObject = cco.object_id FROM sys.key_constraints cco JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id WHERE OBJECT_NAME(parent_object_id) = @TableName AND i.type = 2 AND is_primary_key = 0 AND is_unique_constraint = 1 SET @ClusteredPK = CASE WHEN cv_ROWCOUNT > 0 THEN 1 ELSE 0 END INSERT INTO @Definition(FieldValue) SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' + REVERSE(SUBSTRING(REVERSE(( SELECT c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' FROM sys.key_constraints ccok LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id WHERE i.object_id = ccok.parent_object_id AND ccok.object_id = cco.object_id FOR XML PATH('') )), 2, 8000)) + ')' FROM sys.key_constraints cco LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID LEFT JOIN @Uniques u ON cco.object_id = u.objectID WHERE OBJECT_NAME(cco.parent_object_id) = @TableName END INSERT INTO @Definition(FieldValue) VALUES(')') IF @IncludeIndexes = 1 BEGIN INSERT INTO @Definition(FieldValue) SELECT 'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' + REVERSE(SUBSTRING(REVERSE(( SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' FROM sys.index_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE OBJECT_NAME(sc.object_id) = @TableName AND sc.object_id = i.object_id AND sc.index_id = i.index_id ORDER BY index_column_id ASC FOR XML PATH('') )), 2, 8000)) + ')' FROM sys.indexes i WHERE OBJECT_NAME(object_id) = @TableName AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1 AND is_unique_constraint = 0 AND is_primary_key = 0 END INSERT INTO @MainDefinition(FieldValue) SELECT FieldValue FROM @Definition ORDER BY DefinitionID ASC SELECT * FROM @MainDefinition END GO
Fuente: iomer.com