Another way of accessing data about data is system stored procedures. I will explain the system stored procedures for the above context except for foreign keys as it is a bit complex. Also, while researching the foreign keys system stored procedure I came across the Schema views.

Sp_Tables: This stored procedure returns back all the table names. For example, to get all user defined tables from XYZ database, your SQL will look like –

EXEC sp_tables NULL, dbo, XYZ, "'TABLE'"

Sp_columns: This stored procedure returns back all the columns for specified table. Let’s consider the example above. The best part about this stored procedure is it hides the complexity of joins to get the data types and column names as shown in system tables section. To get all columns in the Users table you will call this procedure as –

Exec sp_columns 'Users'

sp_datatype_info: This stored procedure returns back information on all data types.

Syntax is going to be-

EXEC sp_datatype_info