SQL Server Mentalist

BISQL # 29 : Analysis and Documentation Query

July 25, 2011 4:22 pm

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 |

Posted by Vishal Pawar

Categories: Link, Query, SQL

Tags:

« Older Newer »

5 Responses to “BISQL # 29 : Analysis and Documentation Query”

[…] BISQL # 29 : Analysis and Documentation Query […]

By Link Resource # 6 July 26 « Dactylonomy of Web Resource on July 26, 2011 at 10:51 am
[…] BISQL # 29 : Analysis and Documentation Query […]

By List of monthly post of MS BI and SQL blog–July 2011 « SQL Server Mentalist on July 31, 2011 at 10:11 pm
[…] BISQL # 29 : Analysis and Documentation Query […]

By List of monthly post of MS BI and SQL blog–July 2011 « (B)usiness (I)ntelligence Mentalist on July 31, 2011 at 10:19 pm
[…] BISQL # 29 : Analysis and Documentation Query […]

By List of monthly post of MS BI and SQL blog–July 2011 « Dactylonomy of Web Resource on July 31, 2011 at 10:30 pm
[…] BISQL # 29 : Analysis and Documentation Query […]

By Link Resource # 20 : Aug 26 – Aug 28 « Dactylonomy of Web Resource on August 27, 2011 at 2:26 pm

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.

SQL Server Mentalist

BISQL # 29 : Analysis and Documentation Query

Stay Tuned :

Related

5 Responses to “BISQL # 29 : Analysis and Documentation Query”

Leave a Reply