All Tables and Columns

				
--**************************************
-- Name: All Tables and Columns
-- Description:Store Procedure that display all tables and columns, type, length for any database.
-- By: marisol gonzalez
--
-- Inputs:all optionals, but the order is
'c', <col name> or
't', <table name>
<col name> or <table name> could be full or partial.
--
-- Returns:returns grid.
--
-- Assumes:you must create this sp at master database.
example of use:
with query analyzer at any database, execute the procedure with no parameters, the result is all tables, col´s and types for current database.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=730&lngWId=5--for details.--**************************************

--despliega todas las columnas y tablas en la base de datos 
--parametros:ninguno (por defecto) 
--Opcionales:
--'c'-->ordenado por columnas, 'nombre de col'
--'t' --> ordenado por tabla, 'nombre de tabla'
--mgJulio 24,2003
CREATE PROC sp_columnasytablas
@orden char(1) = null ,
@objeto varchar(255) = null
AS
SET NOCOUNT ON
if ( (@orden is null ) or ( (lower(@orden) <> 'c' ) and (lower(@orden) <> 't' )))
print 'Opciones: c ó nada--> ordenado por columna, t --> ordenado por tabla'
--Búsqueda por defecto---
if(@orden is null )
 goto BuscarPorDefecto
--Búsqueda por columna y nombre de la columna---
if((lower(@orden) = 'c' ) and (@objeto is not null))
 begin
goto BuscarPorColumnaYNombre
 end
--Búsqueda solo por columna ---
else
begin
if(lower(@orden) = 'c' ) 
 goto BuscarSoloPorColumna
end
--Búsqueda por tabla y nombre de la tabla---
if((lower(@orden) = 't' ) and (@objeto is not null))
begin 
goto BuscarPorTablaYNombre
end
--Búsqueda solo por tabla ---
else
 begin
 if(lower(@orden) = 't' )
goto BuscarSoloPorTabla
end
--------------------//-------------------------------
BuscarPorDefecto:
--select 'Por Def'
	select sc.name as 'columna' , so.name as 'tabla', 
st.name as 'tipo', sc.length as 'largo',
 sc.xprec as 'prec', sc.xscale as 'escala'
	from syscolumns sc, sysobjects so, systypes st
	where so.xtype = 'U' and 
	 sc.id = so.id and
 sc.xtype = st.xtype
	order by sc.name, so.name
return
BuscarSoloPorColumna:
--select 'Solo Col'
	select sc.name as 'columna' , so.name as 'tabla', 
st.name as 'tipo', sc.length as 'largo',
 sc.xprec as 'prec', sc.xscale as 'escala'
	from syscolumns sc, sysobjects so, systypes st
	where so.xtype = 'U' and 
	 sc.id = so.id and
 sc.xtype = st.xtype
order by sc.name
return
BuscarPorColumnaYNombre:
--select 'Col y Nom'
	select sc.name as 'columna' , so.name as 'tabla', 
st.name as 'tipo', sc.length as 'largo',
 sc.xprec as 'prec', sc.xscale as 'escala'
	from syscolumns sc, sysobjects so, systypes st
	where so.xtype = 'U' and 
	 sc.id = so.id and
-- sc.name = @objeto and 
 sc.name like @objeto + '%' and 
 sc.xtype = st.xtype
order by sc.name
return
BuscarPorTablaYNombre:
	select so.name as 'tabla', sc.name as 'columna' , 
st.name as 'tipo', sc.length as 'largo',
 sc.xprec as 'prec', sc.xscale as 'escala'
	from syscolumns sc, sysobjects so, systypes st
	where so.xtype = 'U' and 
	 sc.id = so.id and
-- so.name = @objeto and 
 so.name like @objeto + '%' and 
 sc.xtype = st.xtype
order by so.name
return
BuscarSoloPorTabla:
	select so.name as 'tabla', sc.name as 'columna' , 
st.name as 'tipo', sc.length as 'largo',
 sc.xprec as 'prec', sc.xscale as 'escala'
	from syscolumns sc, sysobjects so, systypes st
	where so.xtype = 'U' and 
	 sc.id = so.id and
 sc.xtype = st.xtype
order by so.name
return