Hi. A long, long time ago, I wrote a note for a friend’s web site about SQL Server metadata (in Spanish)

From time to time, there is people asking for it, or for the code of the article.

Here you have two versions of it.

As a Single XML

SELECT
		 TABLE_SCHEMA AS [Schema],
		 TABLE_NAME AS [Name],
  (
	  SELECT
				COLUMN_NAME,
				ORDINAL_POSITION,
				COLUMN_DEFAULT,
				IS_NULLABLE,
				DATA_TYPE,
				CHARACTER_MAXIMUM_LENGTH,
				NUMERIC_PRECISION,
				NUMERIC_PRECISION_RADIX,
				NUMERIC_SCALE,
				DATETIME_PRECISION,
				CHARACTER_SET_NAME,
				COLLATION_NAME,
				CAST(COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS BIT) AS IsIdentity
		  FROM
			  INFORMATION_SCHEMA.COLUMNS AS [Column]
			WHERE [COLUMN].TABLE_NAME = [Table].TABLE_NAME
			 ORDER BY
						 TABLE_SCHEMA,
						 TABLE_NAME,
						 ORDINAL_POSITION FOR
	  XML AUTO, TYPE
  ) AS Columns,
  (
	  SELECT
				PK.COLUMN_NAME,
				PK.ORDINAL_POSITION,
				PK.TABLE_NAME
		  FROM
				 INFORMATION_SCHEMA.TABLE_CONSTRAINTS
				 INNER JOIN
						 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK
				 ON
					  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = PK.CONSTRAINT_NAME
					  AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA = PK.TABLE_SCHEMA
					  AND INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = PK.TABLE_NAME
			WHERE
		  (
						  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY'
						  AND PK.TABLE_NAME = [Table].TABLE_NAME
		  )
			 ORDER BY
						 INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME,
						 PK.ORDINAL_POSITION FOR
			  XML AUTO, TYPE
  ) AS PrimaryKeys,
  (
	  SELECT
				Parent.TABLE_NAME AS ChildTable,
				Parent.COLUMN_NAME AS ChildColumn,
				ParentColumn.TABLE_NAME AS PKTable,
				ParentColumn.COLUMN_NAME AS PKColumn
		  FROM
				 INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
				 INNER JOIN
						 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Parent
				 ON
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_CATALOG = Parent.TABLE_CATALOG
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_SCHEMA = Parent.TABLE_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_NAME = Parent.TABLE_NAME
				 INNER JOIN
						 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
				 ON
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_CATALOG = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_CATALOG
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_SCHEMA = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
				 INNER JOIN
						 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ParentColumn
				 ON
					  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG = ParentColumn.CONSTRAINT_CATALOG
					  AND INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = ParentColumn.
					  CONSTRAINT_SCHEMA
					  AND INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = ParentColumn.CONSTRAINT_NAME
			WHERE
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_CATALOG = [Table].TABLE_CATALOG
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_SCHEMA = [Table].TABLE_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_NAME = [Table].TABLE_NAME FOR
				 XML AUTO, TYPE
  ) AS Parents,
  (
	  SELECT
				Child.TABLE_NAME AS ChildTable,
				Child.COLUMN_NAME AS ChildColumn,
				ChildColumn.TABLE_NAME AS PKTable,
				ChildColumn.COLUMN_NAME AS PKColumn
		  FROM
				 INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
				 INNER JOIN
						 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS Child
				 ON
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_CATALOG = Child.TABLE_CATALOG
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_SCHEMA = Child.TABLE_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_NAME = Child.TABLE_NAME
				 INNER JOIN
						 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
				 ON
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_CATALOG = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_CATALOG
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_SCHEMA = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.CONSTRAINT_NAME = INFORMATION_SCHEMA.
					  REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
				 INNER JOIN
						 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ChildColumn
				 ON
					  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_CATALOG = ChildColumn.CONSTRAINT_CATALOG
					  AND INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_SCHEMA = ChildColumn.
					  CONSTRAINT_SCHEMA
					  AND INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME = ChildColumn.CONSTRAINT_NAME
			WHERE
					  INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_CATALOG = [Table].Table_catalog
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_SCHEMA = [Table].Table_SCHEMA
					  AND INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE.TABLE_NAME = [Table].Table_NAME FOR
				 XML AUTO, TYPE
  ) AS Childs
	FROM
		iNFORMATION_sCHEMA.Tables AS [Table]
	 WHERE
				[Table].TABLE_TYPE = 'BASE TABLE'
				AND [Table].TABLE_NAME NOT LIKE 'sys%'
	  ORDER BY
				  [Table].TABLE_SCHEMA,
				  [Table].TABLE_NAME FOR
		 XML AUTO, TYPE;

As a set of Tables.

-- Tables
SELECT
		 [TABLE_SCHEMA] AS [Schema],
		 [TABLE_NAME] AS [Name]
	FROM
		[INFORMATION_SCHEMA].[TABLES] AS [Table]
	 WHERE
				([TABLE_TYPE] = 'BASE TABLE')
				AND
	([TABLE_NAME] NOT LIKE 'sys%'
	)
				AND
	([TABLE_NAME] NOT LIKE 'AspNet%'
	)
				AND
	([TABLE_NAME] NOT LIKE '__MigrationHistory'
	)
	  ORDER BY
				  [Schema],
				  [Name];
-- Columns
SELECT
		 [COLUMN_NAME],
		 [ORDINAL_POSITION],
		 [COLUMN_DEFAULT],
		 [IS_NULLABLE],
		 [DATA_TYPE],
		 [CHARACTER_MAXIMUM_LENGTH],
		 [NUMERIC_PRECISION],
		 [NUMERIC_PRECISION_RADIX],
		 [NUMERIC_SCALE],
		 [DATETIME_PRECISION],
		 [CHARACTER_SET_NAME],
		 [COLLATION_NAME],
  (
	  SELECT
				[sys].[columns].[is_identity]
		  FROM
				 [sys].[columns]
				 INNER JOIN
						 [sys].[tables]
				 ON [sys].[columns].object_id = [sys].[tables].object_id
			WHERE
					  ([sys].[columns].[name] = [Column].[COLUMN_NAME])
					  AND ([sys].[tables].[name] = [Column].[TABLE_NAME])
  ) AS [IsIdentity],
		 [TABLE_SCHEMA],
		 [TABLE_NAME]
	FROM
		[INFORMATION_SCHEMA].[COLUMNS] AS [Column]
	  ORDER BY
				  [TABLE_SCHEMA],
				  [TABLE_NAME],
				  [ORDINAL_POSITION];
-- Primary Keys
SELECT
		 [PK].[TABLE_SCHEMA],
		 [PK].[COLUMN_NAME],
		 [PK].[ORDINAL_POSITION],
		 [PK].[TABLE_NAME]
	FROM
		  [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [PK]
		  ON
				[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS].[CONSTRAINT_NAME] = [PK].[CONSTRAINT_NAME]
				AND [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS].[TABLE_SCHEMA] = [PK].[TABLE_SCHEMA]
				AND [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS].[TABLE_NAME] = [PK].[TABLE_NAME]
	 WHERE([INFORMATION_SCHEMA].[TABLE_CONSTRAINTS].[CONSTRAINT_TYPE] = 'PRIMARY KEY')
	  ORDER BY
				  [PK].[TABLE_NAME],
				  [PK].[ORDINAL_POSITION];
-- Parents
SELECT
		 [ParentColumn].[TABLE_SCHEMA] AS [ParentSchema],
		 [ParentColumn].[TABLE_NAME] AS [ParentTable],
		 [ParentColumn].[COLUMN_NAME] AS [ParentColumn],
		 [Parent].[TABLE_SCHEMA] AS [ChildSchema],
		 [Parent].[TABLE_NAME] AS [ChildTable],
		 [Parent].[COLUMN_NAME] AS [ChildColumn],
		 [ParentColumn].[ORDINAL_POSITION]
	FROM
		  [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]
		  ON
				[INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_CATALOG] = [INFORMATION_SCHEMA].
				[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_CATALOG]
				AND [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_SCHEMA] = [INFORMATION_SCHEMA].
				[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_SCHEMA]
				AND [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_NAME] = [INFORMATION_SCHEMA].
				[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS [Parent]
		  ON
				[INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_CATALOG] = [Parent].[CONSTRAINT_CATALOG]
				AND [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_SCHEMA] = [Parent].[CONSTRAINT_SCHEMA]
				AND [INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE].[CONSTRAINT_NAME] = [Parent].[CONSTRAINT_NAME]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [ParentColumn]
		  ON
				[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[UNIQUE_CONSTRAINT_CATALOG] = [ParentColumn].
				[CONSTRAINT_CATALOG]
				AND [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[UNIQUE_CONSTRAINT_SCHEMA] = [ParentColumn].
				[CONSTRAINT_SCHEMA]
				AND [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[UNIQUE_CONSTRAINT_NAME] = [ParentColumn].
				[CONSTRAINT_NAME]
	  ORDER BY
				  [ParentTable],
				  [ParentColumn],
				  [ParentColumn].[ORDINAL_POSITION];
-- Childs
SELECT
		 [Child].[TABLE_SCHEMA] AS [ChildSchema],
		 [Child].[TABLE_NAME] AS [ChildTable],
		 [Child].[COLUMN_NAME] AS [ChildColumn],
		 [ParentTable].[TABLE_SCHEMA] AS [ParentSchema],
		 [ParentTable].[TABLE_NAME] AS [ParentTable],
		 [ParentTable].[COLUMN_NAME] AS [ParentColumn],
		 [ParentTable].[ORDINAL_POSITION]
	FROM
		  [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS [ParentTable]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]
		  ON
				[ParentTable].[CONSTRAINT_CATALOG] = [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].
				[UNIQUE_CONSTRAINT_CATALOG]
				AND [ParentTable].[CONSTRAINT_SCHEMA] = [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].
				[UNIQUE_CONSTRAINT_SCHEMA]
				AND [ParentTable].[CONSTRAINT_NAME] = [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].
				[UNIQUE_CONSTRAINT_NAME]
		  INNER JOIN
				  [INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] AS [Child]
		  ON
				[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_CATALOG] = [Child].[CONSTRAINT_CATALOG]
				AND [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_SCHEMA] = [Child].[CONSTRAINT_SCHEMA]
				AND [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS].[CONSTRAINT_NAME] = [Child].[CONSTRAINT_NAME]
	  ORDER BY
				  [ChildTable],
				  [ChildColumn],
				  [ParentTable].[ORDINAL_POSITION];

Hope this Help.

Enjoy!