--**************************************
-- Name: Know metadata sql server Connociendo metadata de sql server
-- Description:Here there is a miscellaneus of querys that let know the structure of the objects and relationships of any BD in SQL Server
These querys can be used in another BD that use the INFORMATION_SCHEMA.
Aqui se encuentra una miscelania de consultas que permiten conocer la estructura de cualquier objeto y sus relaciones en SQL Server. 
Estas consultas pueden ser aplicadas a cualquier motor de BD que maneje la BD INFORMATION_SCHEMA
VOTE FOR ME!!!!
VOTEN POR MI!!!
-- By: Alejo Carrilllo
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1341&lngWId=5--for details.--**************************************

USE AdventureWorks 
GO
-- TABLAS QUE CONTIENEN UNA COLUMNA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CUSTOMERID'
-- COLUMNAS DE UNA TABLA
SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'CUSTOMER'
-- LISTADO DE TABLAS
SELECT * FROM INFORMATION_SCHEMA.TABLES 
-- CAMPOS DE LA PRIMARY KEY EN UNA TABLA DADA
SELECT 
TC.TABLE_NAME, CU.COLUMN_NAME 
FROM 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
INNER JOIN 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND TC.TABLE_NAME = 'CUSTOMER' -- TABLA 
ORDER BY TC.TABLE_NAME
-- RELACION DE TABLA (DEPENDENCIAS)
SELECT 
FK_Table = FK.TABLE_NAME, 
FK_Column = CU.COLUMN_NAME, 
PK_Table = PK.TABLE_NAME, 
PK_Column = PT.COLUMN_NAME, 
ConstraintName = C.CONSTRAINT_NAME 
FROM 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN 
( 
 SELECT 
TC.TABLE_NAME, CU.COLUMN_NAME 
 FROM 
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
 INNER JOIN 
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
 ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
) PT 
ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
PK.TABLE_NAME = 'SALESORDERHEADER' -- TU TABLA
-- RELACION DE TABLA (PADRES)
SELECT 
FK_Table = FK.TABLE_NAME, 
FK_Column = CU.COLUMN_NAME, 
PK_Table = PK.TABLE_NAME, 
PK_Column = PT.COLUMN_NAME, 
ConstraintName = C.CONSTRAINT_NAME 
FROM 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
INNER JOIN 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN 
( 
 SELECT 
TC.TABLE_NAME, CU.COLUMN_NAME 
 FROM 
 INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC 
 INNER JOIN 
 INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
 ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
) PT 
ON PT.TABLE_NAME = PK.TABLE_NAME 
WHERE 
FK.TABLE_NAME = 'clientesp' -- TU TABLA
-- CHECK DE UNA TABLA DADA
SELECT TC.TABLE_SCHEMA,TC.TABLE_NAME,CU.COLUMN_NAME,
CK.CHECK_CLAUSE 
 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON TC.TABLE_NAME = CU.TABLE_NAME 
AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
INNER JOIN
INFORMATION_SCHEMA.CHECK_CONSTRAINTS CK
ON
CK.CONSTRAINT_NAME = TC.CONSTRAINT_NAME 
WHERE CONSTRAINT_TYPE = 'CHECK'
AND
TC.TABLE_NAME = 'CUSTOMER' -- TU TABLA
-- PARAMETROS DE UNA FUNCION O STORE
SELECT *
FROM INFORMATION_SCHEMA.PARAMETERS 
WHERE SPECIFIC_NAME ='ufnGetProductDealerPrice'