Home > Link, Query, SQL > BISQL # 29 : Analysis and Documentation Query

BISQL # 29 : Analysis and Documentation Query

July 25, 2011 Leave a comment Go to comments

 

Hi Friends,

Following are  some useful queries which will help you in documentation as well as in Analysis purpose.

I have given definition of each at start in comments of query !!

--LISTING ALL TABLE INFORMATION BY TABLE NAME COLUMN NAME AND DATA TYPE OTHER DETAILS TOO

        SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
        T.[name] AS [table_name], AC.[name] AS [column_name],   
        TY.[name] AS system_data_type, AC.[max_length],  
        AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]  
        FROM sys.[tables] AS T   
        INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]  
        INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   
        WHERE T.[is_ms_shipped] = 0  
        ORDER BY T.[name], AC.[column_id]

--LISTING ALL THE DMV/DMF USING SYS.ALL_OBJECTS CATALOG VIEW 
        SELECT * FROM sys.all_objects 
        WHERE name LIKE 'dm_%' ORDER BY name 

--Listing all the DMV/DMF using sys.system_objects catalog view 
        SELECT * FROM sys.system_objects 
        WHERE name LIKE 'dm_%' ORDER BY name 

--LISTING ALL THE DMV/DMF ALONG WITH ITS COLUMNS, THEIR DATA TYPES AND SIZE 
        SELECT so.name AS [DMV/DMF], sc.name AS [Column],  
        t.name AS [Data Type], sc.column_id [Column Ordinal],  
        sc.max_length, sc.PRECISION, sc.scale 
        FROM sys.system_objects so 
        INNER JOIN sys.system_columns sc ON so.OBJECT_ID = sc.OBJECT_ID 
        INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id 
        WHERE so.name LIKE 'dm_%'  
        ORDER BY so.name, sc.column_id 
 
--CATCHING QUERY WHICH TAKING LOGER TIME 
        SELECT DB_NAME(database_id) AS [Database], [text] AS [Query]   
        FROM sys.dm_exec_requests r  
        CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st  
        WHERE session_Id > 50 
 
--LISTING OF ALL CURRENT WORKING OPERATION IN DATABASE
        select * from sys.dm_exec_requests

--VIEWING THE DEFINITION OF DMV USING SP_HELPTEXT 
        EXEC sp_helptext 'sys.dm_exec_query_stats' 

--VIEWING THE DEFINITION OF DMV USING OBJECT_DEFINITION FUNCTION 
        SELECT OBJECT_DEFINITION(OBJECT_ID('sys.dm_exec_query_stats'))

--ALL HISTORY REALATED INFORMATION OF SCHEMA 
        SELECT * FROM sys.sysobjects 
        WHERE xtype = 'U'--Without schema name for backward compatibility 
        SELECT * FROM sysobjects 
        WHERE xtype = 'U' 

--LISTING BASIC INFORMATION OF ALL TABLE FROM GIVEN DATABASE
--TYPE-1 
        SELECT * FROM INFORMATION_SCHEMA.TABLES Order by TABLE_TYPE
--TYPE-2
        SELECT * FROM sys.objects 
        WHERE type_desc = 'USER_TABLE' 

--LISTING ALL CONSTARINS OF THE TABLE IN GIVEN DATABASE
        SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

--LISTING OF ALL INFORNATION OF COLOUMN 
        SELECT * FROM INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME 

--LISTING ALL VIEW IN THE DATABASE WITH ALL DETAILS 
        SELECT * FROM INFORMATION_SCHEMA.VIEWS
        SELECT * FROM sys.views 

--LISTING ALL STORED PROC EXIST IN THE DATABSE WITH ALL DAETAILS 
        SELECT * FROM INFORMATION_SCHEMA.ROUTINES

 

This queries are I have already post on my blog in last December :Documentation And Maintenance Useful Queries

Hope this helps !!

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 30 : How Should I Find Information about Data Type

· BISQL # 31 : How To Create Folder from SSMS

· BISQL # 32 : How To find Parent Child Relationship Between Tables

· BISQL # 33 : Lets Understand Stored Procedure

· BISQL # 34 : What is Identity IN SQL {Identity Part–I}

 

Hope you will like this post on Analysis & documentation query.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

               For More information related to BI World visit my Mentalist Blog

                              Link Resource Blog >> Daily Interesting links

                              SQL Server Mentalist >> SQL Learning Blog

                              Business Intelligence Mentalist >> BI World

                                          Connect With me on

               | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                       Copyright © 2011 – 2012 Vishal Pawar

Advertisement
Categories: Link, Query, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s

%d bloggers like this: