MSSqlServer2005 Queries

–HOW CAN WE CHECK ALL PROCEDURES IN OUR DATABASE AND FUNCTIONS WITH QUERY

SELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES ORDER BY 1
——————————————-
–It will show only user stored procedure and functions
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME NOT LIKE ‘sp[_]%’
——————————————-
–view to only stored procedures with query of your database
select * from Sysobjects where xType = ‘P’ and category = 0

——————————————-
SELECT
ROUTINE_NAME
FROM
INFORMATION_SCHEMA.ROUTINES
where
objectproperty(object_id(ROUTINE_NAME),’IsMSShipped’) = 0 and
objectproperty(object_id(ROUTINE_NAME),’IsProcedure’) = 1
order by
ROUTINE_NAME
———————————————-
SELECT name FROM sys.procedures
——————————————-
–how can check data diagram infromation from table with system store p
EXEC SP_HELPDIAGRAMS

–How to Check stroed procedure create modification and name with query
—————————————–
SELECT name, create_date, modify_date FROM sys.objects
WHERE type = ‘P’ AND name = ‘usp_DealingHeadsFloatedTenders’
—————————————–
How can clear and clean to buffer ,cache of a stored procedure
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s