How to Query SQL Server for Table and Field Information?

Querying a Database for Table and Field Information

When creating an ORM, you may need to retrieve a list of tables and the associated fields in each table within a database. To accomplish this in SQL Server, consider utilizing system stored procedures or database views.

Utilizing System Stored Procedures

One method involves employing system stored procedures such as sp_tables to retrieve the list of tables. For each table, you can use sp_columns to obtain the column information.

Example:

EXEC sp_tables @table_type = 'TABLE'

Leveraging Database Views

Alternatively, you can utilize database views like sys.objects and sys.columns to query the object catalog:

Query 1 (Using sys.objects and sys.columns):

SELECT
  T.name AS Table_Name,
  C.name AS Column_Name,
  P.name AS Data_Type,
  C.max_length AS Size,
  CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM
  sys.objects AS T
JOIN
  sys.columns AS C ON T.object_id = C.object_id
JOIN
  sys.types AS P ON C.system_type_id = P.system_type_id
WHERE
  T.type_desc = 'USER_TABLE';

Query 2 (Using INFORMATION_SCHEMA.COLUMNS):

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  COLUMN_NAME,
  ORDINAL_POSITION,
  COLUMN_DEFAULT,
  DATA_TYPE,
  CHARACTER_MAXIMUM_LENGTH,
  NUMERIC_PRECISION,
  NUMERIC_PRECISION_RADIX,
  NUMERIC_SCALE,
  DATETIME_PRECISION
FROM
  INFORMATION_SCHEMA.COLUMNS;

Additional Resources

For further exploration of SQL Server blog resources, consider the following:

• [DBALink](http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/)

The above is the detailed content of How to Query SQL Server for Table and Field Information?. For more information, please follow other related articles on the PHP Chinese website!