USE [METRICAS]
GO

--****** Object      :StoredProcedure [dbo].[PROC_SYS_Campos_Tabla] 
--****** Autor       : Jorge Sánchez   
--****** Script Date : 07/12/2015 09:35:32 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PROC_SYS_Campos_Tabla]
   @Tabla varchar(100)
AS

SELECT DISTINCT
syscolumns.colid AS IdCampo, syscolumns.name AS Campo, systypes.name AS Tipo, 
syscolumns.length AS Longitud, 
CASE 
	WHEN SysColumns.isnullable = 1 THEN 'SI'
	ELSE ''
END AS Permite_Nulo,
CASE
	WHEN sysindexkeys.Keyno IS NULL THEN ''
	ELSE 'SI'
END AS Clave,
CASE
	WHEN sysforeignkeys.fkey IS NULL THEN ''
	ELSE 'SI'
END AS Clave_Externa,
CASE
	WHEN sysobjects_1.name IS NULL THEN ''
	ELSE sysobjects_1.name
END AS Tabla_Externa
FROM         
sysobjects sysobjects_1 RIGHT OUTER JOIN
sysforeignkeys ON sysobjects_1.id = sysforeignkeys.rkeyid RIGHT OUTER JOIN
syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON sysforeignkeys.fkeyid = syscolumns.id AND 
sysforeignkeys.fkey = syscolumns.colid LEFT OUTER JOIN
sysindexkeys ON sysobjects.id = sysindexkeys.id AND syscolumns.colid = sysindexkeys.colid
WHERE  
sysobjects.name = @Tabla AND systypes.name <> 'sysname'
ORDER BY syscolumns.colid

GO