use sucursal1
select * from syscolumns
select * from sysobjects
select * from systypes
--1.- numero de campos por cada tabla
Select [Link] , count(*) as campos from sysobjects as t
inner join syscolumns as c on [Link]=[Link]
where [Link]='u' group by [Link]
--2.- numero de objetos por cada tabla
Select case
when [Link]='F' then 'llave Foranea '
when [Link]='IT' then 'UT'
when [Link]='PK' then 'llave primarias '
when [Link]='S' then 'S '
when [Link]='SQ' then 'sql '
when [Link]='U' then 'Tablas usuario '
when [Link]='V' then 'Vista '
else 'otro'
end as typo_objecto,
count(*) as num_obj
from sysobjects as o
group by [Link]
order by [Link]
--3.- Numero de vistas y numero de tablas de usuario en la base de datos
Select xtype,count(*) as Tablas from sysobjects where xtype='u'
group by xtype
union
Select xtype,count(*) as Vistas from sysobjects where xtype='v'
group by xtype
Select xtype, count(*) as Num_TyV
from sysobjects where xtype in('u', 'v')
group by xtype
-- 4 Cantidad de bytes por cada tabla de usuario
Select [Link], sum(length) as tamTabla
from sysobjects as t inner join syscolumns as c on [Link] =[Link] where [Link]='u'
group by [Link]
order by tamTabla
--5.-Tabla o tablas con mas campos
Select top 1 [Link] , count(*) as campos from sysobjects as t
inner join syscolumns as c on [Link]=[Link]
where [Link]='u' group by [Link]
--6.- Tabla o tablas con mayor nummero de bytes
Select top 1 with ties [Link], sum(length) as tamTabla
from sysobjects as t inner join syscolumns as c on [Link]=[Link]
where [Link]='u'
group by [Link]
order by tamTabla
--7.-Campo mas utilizado en toda la base de datos (Numero de veces )
Select top 1 with ties [Link] as tipo, count(*) as veces from sysobjects as c inner
join syscolumns as t
on [Link]=[Link] Where [Link]='u'
Group by [Link]
Order by veces desc
--8.-Tabla o tablas con mas llaves foraneas
Select top 1 with ties [Link] as llaves, count(*) as num_llaves
from sysobjects as o, sysobjects as f
where [Link]=f.parent_obj and [Link]='u' and [Link]='PK'
group by [Link]
order by num_llaves desc
--9.-Tabla o tablas con mas llaves primarias
select top 1 with ties [Link] as tabla, count(*) as FKs
from sysobjects as o, sysobjects as f
where [Link]=f.parent_obj and [Link]='u' and [Link]='PK'
group by [Link]
order by FKs desc
--10.- Tipos de datos no utilizados en la base de datos
select [Link] from systypes as t where xtype not in(select distinct xtype from
syscolumns )
--11.-saber si existe una tabla
Select [Link], [Link] from sysobjects as t where EXISTS (Select * from sysobjects
as o where [Link]='u' and [Link]='articulos')
--12.- saber si existe un campo en una tabla determinada
Select [Link] from syscolumns as c
inner join sysobjects as t on [Link]=[Link] where [Link]='articulos' and exists (Select
[Link] from sysobjects where [Link] ='activo' )
group by [Link]