SQL SERVER HELP

Get All User Defined Stored Procedures List

It gives only SpNames

SELECT  DISTINCT(sysobjects.name)

                FROM

                        sysobjects,syscomments

                WHERE

                        sysobjects.id =     syscomments.id

                AND

                        sysobjects.type = 'P'

                AND

                        sysobjects.category=0

(OR)

It gives SpNames and also Defination

select * from sysobjects where type='p'

Get specific word used in User Defined Stored Procedures List

It gives only SpNames

SELECT Name

FROM sys.procedures

WHERE OBJECT_DEFINITION(object_id) LIKE '%SELECT * FROM Category WHERE PKCategoryID = @PKCategoryID%'

(OR)

It gives SpNames and also Defination

select object_name(o.object_id), m.*

from sys.sql_modules m

join sys.objects o on m.object_id = o.object_id

where o.type= 'P'

and m.definition like N'%SELECT * FROM Category WHERE PKCategoryID = @PKCategoryID%'

Get All Databases Names

SELECT *

FROM sys.Databases

Get All Tables Names

SELECT *

FROM sys.Tables

(OR)

select * from sysobjects where type='U' order by name

This will gives the exact user tables from database

SELECT * FROM sysobjects

WHERE

type='U'

and

name != 'dtproperties'

ORDER BY [name]

This Querry to show the all column count from a Specific Table

SELECT COUNT(*)

FROM EasyShopJuJuBe_new_29July2011.sys.columns

WHERE object_id = OBJECT_ID('EasyShopJuJuBe_new_29July2011.dbo.Category')

This Querry to fetch all functions(userdefined or system) in a database:

select * from sysobjects where type='fn'

This Querry to fetch all (Tables Names,columnNames,Datatype and Size) in a database:

SELECT SysObjects.[Name] as TableName, SysColumns.[Name] as ColumnName,

SysTypes.[Name] As DataType,SysColumns.[Length] As Length FROM SysObjects INNER JOIN SysColumns

ON SysObjects.[Id] = SysColumns.[Id] INNER JOIN SysTypes ON SysTypes.[xtype] = SysColumns.[xtype]

WHERE SysObjects.[type] = 'U' ORDER BY SysObjects.[Name]

This Querry to find All Triggers in a database:

select * from sysobjects where type='tr'

(OR)

select * from sys.triggers