Introduction The most of the time we need play with view and stored procedures in sql server databases.Sometime we need to know few important queries or result in sql server. 1.List of Views 2.List of Procedures 3.List of Tables 4. Find a Table 5.Find column in Tables 6.Get usefull information related tables. Implementation I'm going to give answer to above list of questions,lets say we have created a database there lot's of views, how do i list all views easliy and find a view in easier way. To this we could use the SSMS and by the sql query also. Here we will how we can get by the query. 1.List Of Views The Query like {codecitation class="brush: sql; gutter: true;" width="650px"}
SELECT name,crdate FROM sysobjects WHERE xtype = 'U'
Order by crdate DESC {/codecitation} Result 
See above query read all object name and created date from the sysObjects which is XTYPE equal to 'V' it's means views. 2.List of all Stored Procedures To this we could use the same query which written in earlier example, but we need change the XType from V to P. P means system type value for Stored Procedures. {codecitation class="brush: sql; gutter: true;" width="650px"} SELECT name,crdate FROM sysobjects WHERE xtype = 'P'
Order by crdate DESC {/codecitation} Result 
3. List of Tables. To this your write like this way, {codecitation class="brush: sql; gutter: true;" width="650px"} SELECT name as Name,crdate as CreatedDate FROM sysobjects WHERE xtype = 'U'
Order by crdate DESC {/codecitation} Result 
4.Find table using name in sql query. To this write query like this way {codecitation class="brush: sql; gutter: true;" width="650px"} SELECT name as Name,crdate as CreatedDate FROM sysobjects WHERE xtype = 'U'
and [name] LIKE '%user%'
Order by crdate DESC {/codecitation} In above query , i just to find all table which are contains name like user. it's return values like followings. Result 
5. Find a cloumn in Tale. Yes this we need most of the time when we are work sql server database migration or reporting times. To this we need write query to like followings. {codecitation class="brush: sql; gutter: true;" width="650px"} SELECT sysTable.name AS table_name,sysColumns.name AS column_name
FROM sys.tables AS sysTable INNER JOIN sys.columns sysColumns ON sysTable.OBJECT_ID = sysColumns.OBJECT_ID
WHERE sysColumns.name LIKE '%UserCode%'
ORDER BY table_name; {/codecitation} In Above query select table name ,column name from the system table and system column which object id eqaul in both tables. then add like statement to find column name. finally display result with order cluase by Table Name; Result 
6. Finally get all usefull information about tables. To this we write a query to get table informations. say parent table name, created date, user id and etc.This is help to us find relation ship tables {codecitation class="brush: sql; gutter: true;" width="650px"} SELECT name,crdate as CreatedDate ,xtype as [Type],
uid as [user ID],parent_obj as ParentTable FROM sysobjects WHERE xtype = 'U' {/codecitation} Result 
Conclusion This article explorer , about get list of views , stored procedures and tables. It's has query to find a table using name and also find column using column ane in all table in database.i hope this is help to lot. Thank you RRaveen |