Common Database Object queries (User tables, all views ...)

Thanks to csdnShmilywcd(SMILE ))

Original post http://topic.csdn.net/u/20100729/15/62fd8dee-13ba-4bec-95a8-b14c564fde32.html

-- Query User table Object Information Select tab. name as [Table name], Tab. create_date as [creation time], Tab. modify_date as [last modification time], Col. name as [column name], type. name as [data type], Col. max_length as [Field Length], case when PK. is_primary_key = 1 then 'y' else 'n' end as [primary key?], case when Col. is_identity = 1 then 'y' else 'n' end as [auto increment?], identity_columns.seed_value as [auto increment seed], identity_columns.increment_value as [auto increment step], case when Col. is_nullable = 1 then 'y' else 'n' end as [whether to allow null], def. text as [default value], case when Col. is_computed = 1 then 'y' else 'n' end as [whether to calculate the column], computed_columns.definition as [calculation formula], col_desc.value as [column Remarks] From sys. objects tab inner join sys. columns Col on tab. object_id = Col. object_id inner join sys. types type on col. system_type_id = type. system_type_id left join sys. identity_columns on tab. object_id = identity_columns.object_id and Col. column_id = identity_columns.column_id left join syscomments def on col. default_object_id = def. id left join (select index_columns.object_id, index_columns.column_id, indexes. is_primary_key from sys. indexes indexes inner join sys. index_columns on indexes. object_id = index_columns.object_id and indexes. index_id = index_columns.index_id where indexes. is_primary_key = 1/* Primary Key */) PK on tab. object_id = PK. object_id and Col. column_id = PK. column_id left join sys. computed_columns on tab. object_id = computed_columns.object_id and Col. column_id = computed_columns.column_id left join sys. extended_properties col_desc on col_desc.major_id = tab. object_id and col_desc.minor_id = Col. column_id and col_desc.class = 1 where tab. type = 'U' and tab. name Not Like 'sys % 'order by tab. create_date -- Query all views select views. name as [view name], Col. name as [column name], type. name as [data type], Col. max_length as [Field Length] --, col_desc.value as col_description from sys. views views inner join sys. columns Col on views. object_id = Col. object_id inner join sys. types type on col. system_type_id = type. system_type_id -- left join sys. extended_properties col_desc -- On col_desc.major_id = views. object_id and col_desc.minor_id = Col. column_id and col_desc.class = 1 order by create_date -- Query foreign key constraints select fk_name as [foreign key name], parent_tab_name as [foreign key table], [foreign key column] = stuff (select ',' + [parent_col_name] From (select FK. name as fk_name, parent_tab.name as parent_tab_name, parent_col.name as parent_col_name, referenced_tab.name as referenced_tab_name, referenced_col.name as referenced_col_name from sys. foreign_keys FK inner join sys. foreign_key_columns Col on FK. object_id = Col. constraint_object_id inner join sys. objects parent_tab on col. parent_object_id = parent_tab.object_id and parent_tab.type = 'U' inner join sys. columns parent_col on parent_tab.object_id = parent_col.object_id and Col. parent_column_id = parent_col.column_id inner join sys. objects referenced_tab on col. referenced_object_id = referenced_tab.object_id and referenced_tab.type = 'U' inner join sys. columns referenced_col on referenced_tab.object_id = referenced_col.object_id and Col. referenced_column_id = referenced_col.column_id) t where fk_name = TB. fk_name and parent_tab_name = TB. parent_tab_name and referenced_tab_name = TB. referenced_tab_name for XML Path (''), 1, 1,''), referenced_tab_name as [primary key table], [primary key column] = stuff (select ', '+ [referenced_col_name] From (select FK. name as fk_name, parent_tab.name as parent_tab_name, parent_col.name as parent_col_name, referenced_tab.name as referenced_tab_name, referenced_col.name as referenced_col_name from sys. foreign_keys FK inner join sys. foreign_key_columns Col on FK. object_id = Col. constraint_object_id inner join sys. objects parent_tab on col. parent_object_id = parent_tab.object_id and parent_tab.type = 'U' inner join sys. columns parent_col on parent_tab.object_id = parent_col.object_id and Col. parent_column_id = parent_col.column_id inner join sys. objects referenced_tab on col. referenced_object_id = referenced_tab.object_id and referenced_tab.type = 'U' inner join sys. columns referenced_col on referenced_tab.object_id = referenced_col.object_id and Col. referenced_column_id = referenced_col.column_id) t where fk_name = TB. fk_name and parent_tab_name = TB. parent_tab_name and referenced_tab_name = TB. referenced_tab_name for XML Path (''), 1, 1,'') -- as [foreign key column] From (select FK. name as fk_name, parent_tab.name as parent_tab_name, parent_col.name as parent_col_name, referenced_tab.name as referenced_tab_name, referenced_col.name as referenced_col_name from sys. foreign_keys FK inner join sys. foreign_key_columns Col on FK. object_id = Col. constraint_object_id inner join sys. objects parent_tab on col. parent_object_id = parent_tab.object_id and parent_tab.type = 'U' inner join sys. columns parent_col on parent_tab.object_id = parent_col.object_id and Col. parent_column_id = parent_col.column_id inner join sys. objects referenced_tab on col. referenced_object_id = referenced_tab.object_id and referenced_tab.type = 'U' inner join sys. columns referenced_col on referenced_tab.object_id = referenced_col.object_id and Col. referenced_column_id = referenced_col.column_id) TB group by fk_name, parent_tab_name, referenced_tab_name -- Query all stored procedures select pr_name as [Stored Procedure], [parameter] = stuff (select ', '+ [parameter] From (select PR. name as pr_name, parameter. name + ''+ type. name + '(' + convert (varchar (32), parameter. max_length) + ')' as parameter from sys. procedures PR left join sys. parameters parameter on PR. object_id = parameter. object_id inner join sys. types type on parameter. system_type_id = type. system_type_id where type = 'P') t where pr_name = TB. pr_name for XML Path (''), 1, 1,'') from (select PR. name as pr_name, parameter. name + ''+ type. name + '(' + convert (varchar (32), parameter. max_length) + ')' as parameter from sys. procedures PR left join sys. parameters parameter on PR. object_id = parameter. object_id inner join sys. types type on parameter. system_type_id = type. system_type_id where type = 'P') TB where pr_name not like 'SP _ % '-- and pr_name not like 'dt %' group by pr_name order by pr_name -- Query all triggers select triggers. name as [Trigger], tables. name as [Table name], triggers. is_disabled as [disabled], triggers. is_instead_of_trigger as [Trigger type], case when triggers. is_instead_of_trigger = 1 then 'instead of 'when triggers. is_instead_of_trigger = 0 then' after 'else null end as [Trigger type description] From sys. triggers triggers inner join sys. tables tables on triggers. parent_id = tables. object_id where triggers. type = 'tr 'order by triggers. create_date -- Query all indexes select indexs. tab_name as [Table name], indexs. index_name as [index name], indexs. [co_names] as [index column], stored as [primary key], ind_attribute.is_unique as [unique key], ind_attribute.is_disabled as [disabled] From (select tab_name, index_name, [co_names] = stuff (select ',' + [co_name] From (select tab. name as tab_name, Ind. name as index_name, Col. name as co_name from sys. indexes ind inner join sys. tables tab on ind. object_id = tab. object_id and ind. type in (1, 2)/* index type: 0 = Heap/1 = clustering/2 = non-clustering/3 = xml */inner join sys. index_columns on tab. object_id = index_columns.object_id and ind. index_id = index_columns.index_id inner join sys. columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col. column_id) t where tab_name = TB. tab_name and index_name = TB. index_name for XML Path (''), 1, 1,'') from (select tab. name as tab_name, Ind. name as index_name, Col. name as co_name from sys. indexes ind inner join sys. tables tab on ind. object_id = tab. object_id and ind. type in (1, 2)/* index type: 0 = Heap/1 = clustering/2 = non-clustering/3 = xml */inner join sys. index_columns on tab. object_id = index_columns.object_id and ind. index_id = index_columns.index_id inner join sys. columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col. column_id) TB where tab_name not like 'sys % 'group by tab_name, index_name) indexs inner join sys. indexes ind_attribute on indexs. index_name = ind_attribute.name order by indexs. tab_name