--Validar tamaño del log de transacciones--
Select name
,physical_name
,(size/1024) Tamaño_Actual_MB
,max_size Tamaño_Maximo_KB
,growth Crecimiento_Autom
,is_percent_growth
from sys.database_files
--BUSCAR TABLAS EN LA BD--
Select name as object_name
,SCHEMA_NAME(schema_id) as schema_name
,type_desc
,create_date
,modify_date
From sys.objects
Where name like 'vol%'
and type_desc = 'USER_TABLE'
Order by modify_date desc;
--Últimos accesos a una tabla (SE DEBEN TENER PERMISOS)--
SELECT tab.name AS Tablename,
user_seeks, user_scans, user_lookups, user_updates,
last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.tables tab ON (tab.object_id = ius.object_id)
WHERE database_id = DB_ID(N'nombre_bd')
AND tab.name = 'nombre_tabla'
--BUSCAR COLUMNAS EN TABLAS DE LA BD--
SELECT TABLE_NAME,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%migra%'
--FILEGROUPS--
SELECT *
FROM sys.filegroups
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('nombre_bd');
--Tablas por filegroup--
SELECT o.[name] AS 'TABLE',
f.[name] AS 'FILE_GROUP'
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE o.type = 'U' andi.type < 2
Order by O.name
--CALCULAR FECHA DE NACIMIENTO--
SELECT (((365* year(getdate()))-(365*(year(FECHA_NACIMIENTO))))+ (month(getdate())-month(FECHA_NACIMIENTO))*30 +(day(getdate()) - day(FECHA_NACIMIENTO)))/365
--INFORME TAMAÑO TABLAS BD--
select sysobjects.name
, sum(case when sysindexes.indid<2 then rows else 0 end) as rows
, sum(case when sysindexes.indid in (0,1,255) then sysindexes.reserved else 0 end) * 8 as reserved
, sum(case when sysindexes.indid in (0,1) then sysindexes.dpages when sysindexes.indid=255 then sysindexes.used else 0 end) * 8 as Data
, (sum(case when sysindexes.indid in (0,1,255) then sysindexes.used else 0 end) - sum(case when sysindexes.indid in (0,1) then sysindexes.dpages when sysindexes.indid=255 then sysindexes.used else 0 end))*8 as index_size
, (sum(case when sysindexes.indid in (0,1,255) then sysindexes.reserved-sysindexes.used else 0 end)*8) as unused
from sysobjects join sysindexes on sysobjects.id=sysindexes.id
where xtype='U'
group by sysobjects.name
order by 2 desc
--es equivalente a:
sp_spaceused [nombre_tabla]
--GENERACION SCRIPT PARA INFORME TAMAÑO TABLAS BD (se debe ejecutar mostrando los resultados en Text y no en Grid para poderlo copiar para ejecutar)--
SELECT 'sp_columns ' + o.[name] AS 'TABLE', CHAR(13) + CHAR(10) + 'go'
FROM sys.indexes i INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id INNER JOIN sys.objects o
ON i.[object_id] = o.[object_id]
WHERE o.type = 'U' and i.type < 2
Order by O.name
--concatenar registros de un campo en un solo registro--
declare @cadena varchar(1000)
select @cadena = ''
select @cadena = @cadena + ' - ' + Color
from Colores
select Cadena_De_Colores = right(@cadena, len(@cadena) - 3)
--Conocer el porcentaje de fragmentacion de los indices--
WITH INDICES (BD, INDICETIPO, FRAGMENTACION, INDICE, TABLA)
AS (
SELECT DBS.NAME BASEDEDATOS, PS.INDEX_TYPE_DESC, PS.AVG_FRAGMENTATION_IN_PERCENT,
IND.NAME INDICE, TAB.NAME TABLA
FROM
SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) PS
INNER JOIN SYS.DATABASES DBS
ON PS.DATABASE_ID = DBS.DATABASE_ID
INNER JOIN SYS.INDEXES IND
ON PS.OBJECT_ID = IND.OBJECT_ID
INNER JOIN SYS.TABLES TAB
ON TAB.OBJECT_ID = IND.OBJECT_ID
WHERE IND.NAME IS NOT NULL AND PS.INDEX_ID = IND.INDEX_ID
AND PS.AVG_FRAGMENTATION_IN_PERCENT > 0)
SELECT DISTINCT
CASE
WHEN FRAGMENTACION > 5 AND FRAGMENTACION <= 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REORGANIZE'
WHEN FRAGMENTACION > 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REBUILD'
END QUERY, FRAGMENTACION, BD, INDICE, TABLA
FROM (SELECT FRAGMENTACION, INDICE, TABLA, BD FROM INDICES
WHERE FRAGMENTACION > 5) A
ORDER BY FRAGMENTACION DESC
