3 SQL views to deal with all SQL Server database dictionaries

There are many database zones on the Internet. yesky. the SQL statements in the comdevsjktarget_balanksqlserver database dictionary are neat and neat. I have sorted out some ideas in my work and summarized the SQL code as follows. I only passed the test in SQLSERVER2000, hoping to help you. 1. SqlServer database dictionary-table structure.

There are a lot of database areas on the Internet href = "http://dev.yesky.com/devsjk" target = _ blank> SQL server database dictionary SQL statements, seven or eight fall, I have sorted out the train of thought, Summary of SQL code is as follows, I only passed the test in sqlserver2000and hope to help you. 1. SqlServer database dictionary-table structure.

There are manyDatabaseZone href = "http://dev.yesky.com/devsjk" target = _ blank> SQL SERVERDatabaseDictionaryThe SQL statements are neat and neat. I have sorted out some ideas in my work and summarized the SQL code as follows. I only passed the test in SQLSERVER2000, hoping to help you.

　　1. SqlServerDatabaseDictionary-- Table structure. SQL

The following is a reference clip: Select top 100 PERCENT -- a. id, Case when a. colorder = 1 THEN d. name ELSE ''end as table name, Case when a. colorder = 1 THEN isnull (f. value, '') ELSE'' end as table description, A. colorder as field No., a. name AS field name, case when columnproperty (a. id, A. name, 'isidentity ') = 1 then' √ 'else' end as id, CASE WHEN EXISTS (SELECT 1 FROM dbo. sysindexes si INNER JOIN Dbo. sysindexkeys sik ON si. id = sik. id AND si. indid = sik. indid INNER JOIN Dbo. syscolumns SC ON SC. id = sik. id AND SC. colid = sik. colid INNER JOIN Dbo. sysobjects so ON so. name = so. name AND so. xtype = 'pk' WHERE SC. id = a. id AND SC. colid = a. colid) then' √ 'else' END AS primary key, B. name AS type, a. length AS length, COLUMNPROPERTY (a. id, a. name, 'precision ') AS precision, ISNULL (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) AS decimal places, Case when a. isnullable = 1 THEN '√ 'else' end as allow null, ISNULL (e. text ,'') AS default value, ISNULL (g. [value], '') AS field description, d. crdate AS creation time, Case when a. colorder = 1 THEN d. refdate else null end as change time FROM dbo. syscolumns a LEFT OUTER JOIN Dbo. policypes B ON a. xtype = B. xusertype INNER JOIN Dbo. sysobjects d ON a. id = d. id AND d. xtype = 'U' AND D. status> = 0 LEFT OUTER JOIN Dbo. syscomments e ON a. cdefault = e. id LEFT OUTER JOIN Dbo. sysproperties g ON a. id = g. id AND a. colid = g. smallid LEFT OUTER JOIN Dbo. sysproperties f ON d. id = f. id AND f. smallid = 0 Order by d. name, a. colorder

　　2. SqlServerDatabaseDictionary-- Index. SQL

The following is a reference clip: Select top 100 PERCENT -- a. id, Case when B. keyno = 1 THEN c. name ELSE ''END AS table name, Case when B. keyno = 1 THEN a. name ELSE ''END AS index name, d. name AS column name, B. keyno AS index order, CASE indexkey_property (c. id, B. indid, B. keyno, 'isdesending ') WHEN 1 THEN 'descending order 'when 0 then' Ascending Order 'end AS sorting, case when p. id IS NULL THEN ''else' √ 'end AS primary key, case indexproperty (c. id, a. name, 'isclustered ') WHEN 1 THEN '√ 'when 0 then'' end as aggregation, case indexproperty (c. id, A. name, 'isunique') WHEN 1 then' √ 'when 0 then'' end as unique, Case when e. id is null then ''else' √ 'end AS unique constraint, A. OrigFillFactor AS fill factor, c. crdate AS creation time, c. refdate AS change time FROM dbo. sysindexes a INNER JOIN Dbo. sysindexkeys B ON a. id = B. id AND a. indid = B. indid INNER JOIN Dbo. syscolumns d ON B. id = d. id AND B. colid = d. colid INNER JOIN Dbo. sysobjects c ON a. id = c. id AND c. xtype = 'U' LEFT OUTER JOIN Dbo. sysobjects e ON e. name = a. name AND e. xtype = 'uq' LEFT OUTER JOIN Dbo. sysobjects p ON p. name = a. name AND p. xtype = 'pk' WHERE (OBJECTPROPERTY (a. id, N 'isusertable') = 1) AND (OBJECTPROPERTY (a. id, N 'ismsshipped ') = 0) AND (INDEXPROPERTY (a. id, a. name, 'isautostatistics') = 0) Order by c. name, a. name, B. keyno

　　3. SqlServerDatabaseDictionary-- Primary key. Foreign key. constraint.View. Function. Stored Procedure. Trigger. SQL

The following is a reference clip: SELECT DISTINCT TOP 100 PERCENT o. xtype, CASE o. xtype WHEN 'X' extended stored procedure 'when' tr'then' trigger 'when' pK' THEN The 'when' f'then' foreign key 'when 'C' constraint 'when' v'then'View'When' FN' Then' function-scalar 'when' IF 'then' function-nested 'when' TF 'then' function-Table value 'else' Stored Procedure' End as type, o. name AS object name, o. crdate AS creation time, o. refdate AS change time, C. text AS statement FROM dbo. sysobjects o LEFT OUTER JOIN Dbo. syscomments c ON o. id = c. id WHERE (o. xtype IN ('x', 'tr', 'C', 'V', 'F', 'if', 'tf', 'fn, 'P ', 'pk') AND (OBJECTPROPERTY (o. id, n' IsMSShipped ') = 0) Order by case o. xtype WHEN 'X' then' extended stored procedure 'when' TR 'then' trigger 'when 'Pk 'then' primary key 'when' f'then' foreign key 'when'c' then' constraint 'when' v'then'View' WHEN 'fn 'then' function-scalar 'when' IF 'then' function-nested 'when' TF 'then' function-Table value' ELSE 'stored process' END DESC