SQL statements for viewing data tables and fields (applicable to SQLServer2000)

After analyzing the SQLServer2000 system table, the following two SQL statements are written. You can create two "views" for the two statements to view the user data table and field information.

1. List all user data tables:

Select top 100 PERCENT o. name AS table name
FROM dbo. syscolumns c INNER JOIN
Dbo. sysobjects o ON o. id = c. id AND objectproperty (o. id, N 'isusertable') = 1 AND
O. name <> 'dtproperties' LEFT OUTER JOIN
Dbo. sysproperties m ON m. id = o. id AND m. smallid = c. colorder
WHERE (c. colid = 1)
Order by o. name, c. colid

2. List all user data tables and their field information:

Select top 100 PERCENT c. colid AS No., o. name AS table name, c. name AS column name,
T. name AS type, c. length AS length, c. isnullable AS can be empty,
CAST (m. [value] AS Varchar (100) AS description
FROM dbo. syscolumns c INNER JOIN
Dbo. sysobjects o ON o. id = c. id AND objectproperty (o. id, N 'isusertable') = 1 AND
O. name <> 'dtproperties' INNER JOIN
Dbo. policypes t ON t. xusertype = c. xusertype LEFT OUTER JOIN
Dbo. sysproperties m ON m. id = o. id AND m. smallid = c. colorder
Order by o. name, c. colid