Listar Nombres de Tablas y Columnas en SQL Server

Siempre me olvido de como se listan las tablas y las columnas en SQL Server y es algo realmente útil a la hora de generar código.

Veamos como se comportan las siguientes instrucciones contra la base de datos de AdventureWorks

SELECT * FROM INFORMATION_SCHEMA.TABLES

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
ADVENTUREWORKS	Production	ProductProductPhoto	BASE TABLE
ADVENTUREWORKS	Sales	StoreContact	BASE TABLE
ADVENTUREWORKS	Person	Address	BASE TABLE
ADVENTUREWORKS	Production	ProductReview	BASE TABLE
ADVENTUREWORKS	Production	TransactionHistory	BASE TABLE
ADVENTUREWORKS	Person	AddressType	BASE TABLE
ADVENTUREWORKS	Production	ProductSubcategory	BASE TABLE
ADVENTUREWORKS	dbo	AWBuildVersion	BASE TABLE
ADVENTUREWORKS	Production	TransactionHistoryArchive	BASE TABLE

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE Table_Name = 'Product'

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE
ADVENTUREWORKS	Production	Product	ProductID	1	NULL	NO	int
ADVENTUREWORKS	Production	Product	Name	2	NULL	NO	nvarchar
ADVENTUREWORKS	Production	Product	ProductNumber	3	NULL	NO	nvarchar
ADVENTUREWORKS	Production	Product	MakeFlag	4	((1))	NO	bit
ADVENTUREWORKS	Production	Product	FinishedGoodsFlag	5	((1))	NO	bit
ADVENTUREWORKS	Production	Product	Color	6	NULL	YES	nvarchar
ADVENTUREWORKS	Production	Product	SafetyStockLevel	7	NULL	NO	smallint
ADVENTUREWORKS	Production	Product	ReorderPoint	8	NULL	NO	smallint
ADVENTUREWORKS	Production	Product	StandardCost	9	NULL	NO	money
ADVENTUREWORKS	Production	Product	ListPrice	10	NULL	NO	money
ADVENTUREWORKS	Production	Product	Size	11	NULL	YES	nvarchar
ADVENTUREWORKS	Production	Product	SizeUnitMeasureCode	12	NULL	YES	nchar
ADVENTUREWORKS	Production	Product	WeightUnitMeasureCode	13	NULL	YES	nchar
ADVENTUREWORKS	Production	Product	Weight	14	NULL	YES	decimal
ADVENTUREWORKS	Production	Product	DaysToManufacture	15	NULL	NO	int
ADVENTUREWORKS	Production	Product	ProductLine	16	NULL	YES	nchar
ADVENTUREWORKS	Production	Product	Class	17	NULL	YES	nchar
ADVENTUREWORKS	Production	Product	Style	18	NULL	YES	nchar
ADVENTUREWORKS	Production	Product	ProductSubcategoryID	19	NULL	YES	int
ADVENTUREWORKS	Production	Product	ProductModelID	20	NULL	YES	int
ADVENTUREWORKS	Production	Product	SellStartDate	21	NULL	NO	datetime
ADVENTUREWORKS	Production	Product	SellEndDate	22	NULL	YES	datetime
ADVENTUREWORKS	Production	Product	DiscontinuedDate	23	NULL	YES	datetime
ADVENTUREWORKS	Production	Product	rowguid	24	(newid())	NO	uniqueidentifier
ADVENTUREWORKS	Production	Product	ModifiedDate	25	(getdate())	NO	datetime

En el listado de columnas no he sacado todos los campos solo los primeros, porque la información que saca es muy grande, pero esta llega para hacernos una idea de lo que podemos hacer.

En el listado de tablas (como os podéis imaginar) he recortado el número de registros mostrados ;)