--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
