Search Text Used in Store Procedure, Functions or Views in a Database

To search a specific text in all store procedures and functions is common task. It can be achieved by following simple query.



SELECT sys.schemas.name +'.'+OBJECT_NAME(id)

FROM syscomments INNER JOIN sys.objects ON syscomments.id = sys.objects.object_id

INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

WHERE [text] LIKE N'%YOUR TEXT HERE%'

AND (OBJECTPROPERTY(id, 'IsProcedure') = 1

      OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1

      OR OBJECTPROPERTY(id, 'IsView') = 1)

GROUP BY sys.schemas.name +'.'+OBJECT_NAME(id)

A Quick Intorduction to tempdb

TempDB is one of system databases to manage the SQL Server instance. Following is a quick introduction to tempdb:

• SQL Server uses tempdb to store internal objects such as the intermediate results of a query
• tempdb does not persist after SQL Server shuts down
• Each time SQL Server restarts, tempdb is copied from the model database
• Only one file group in tempdb is allowed for data and one file group for logs
• Auto grow is temporary for tempdb (unlike other types of databases). It is reset when SQL Server restarts
• . In a user database, transactions have the ACID attributes: atomicity, concurrency, isolation, and durability. In tempdb, transactions lose the durability attribute
• Auto shrink is not allowed for tempdb
• The database CHECKSUM option cannot be enabled.
• A database snapshot cannot be created on tempdb.
• DBCC CHECKALLOC and DBCC CHECKCATALOG are not supported.
• Only offline checking for DBCC CHECKTABLE is performed. This means that a TAB-S lock is needed. There are internal consistency checks that occur when tempdb is in use. If these checks fail, the user connection is broken and the tempdb space used by the connection is freed.

Which objects occupy tempdb space

The following types of objects can occupy tempdb space:

• Internal objects
• Version stores
• User objects

Internal objects are used:

• To store intermediate runs for sort.
• To store intermediate results for hash joins and hash aggregates.
• To store XML variables or other large object (LOB) data type variables. The LOB data type includes all of the large object types: text, image, ntext, varchar(max), varbinary(max), and all others.
• By queries that need a spool to store intermediate results.
• By keyset cursors to store the keys.
• By static cursors to store a query result.
• By Service Broker to store messages in transit.
• By INSTEAD OF triggers to store data for internal processing.

Must remember that:

• Updates to internal objects do not generate log records
• Each internal object occupies at least nine pages (one IAM page and eight data pages) in tempdb

Version store

• Version stores are used to store row versions generated by transactions for features such as snapshot isolation, triggers, MARS (multiple active result sets), and online index build
• Inserts into the version store do not generate log records
• INSTEAD OF triggers do not generate versions

User objects

• Operations on user objects in tempdb are mostly logged.
• Bulk copy program (BCP), bulk insert, SELECT INTO, and index rebuild operations are bulk logged. This is exactly the same as in other databases with the recovery model set to simple

tempdb Space Requirements

It is difficult to estimate the tempdb space requirement for an application.

But it is recommend that you always have a safety factor of about 20% more space

Physical size of each table of database

Following script calculates and returns all database tables’ size and total number of rows of each table.

USE [YourDatabaseName]

GO

CREATE TABLE #temp (

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp

Commonly Used KeyBoard Shortcut Keys for Sql Server 2005

Menu Activation Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Move to the SQL Server Management Studio menu bar	ALT	ALT
Activate the menu for a tool component	ALT+HYPHEN	ALT+HYPHEN
Display the context menu	SHIFT+F10	SHIFT+F10
Display the New File dialog box to create a file	CTRL+N	No equivalent
Display the New Project dialog box to create a new project	CTRL+SHIFT+N	CTRL+SHIFT+N
Display the Open File dialog box to open an existing file	CTRL+O	CTRL+SHIFT+INS
Display the Open Project dialog box to open an existing project	CTRL+SHIFT+O	No equivalent
Display the Add New Item dialog box to add a new file to the current project	CTRL+SHIFT+A	No equivalent
Display the Add Existing Item dialog box to add an existing file to the current project	ALT+SHIFT+A	No equivalent
Display the Query Designer	CTRL+SHIFT+Q	CTRL+SHIFT+Q
Close a menu or dialog box, canceling the action	ESC	ESC

Windows Management and Toolbar Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Close the current MDI child window	CTRL+F4	CTRL+F4
Print	CTRL+P	CTRL+P
Exit	ALT+F4	ALT+F4
Toggle full screen mode	SHIFT+ALT+ENTER	SHIFT+ALT+ENTER
Close the current tool window	SHIFT+ESC	SHIFT+ESC
Cycle through the next MDI child windows	CTRL+TAB	CTRL+TAB
Cycle through the previous MDI child windows	CTRL+SHIFT+TAB	CTRL+SHIFT+TAB
Move to the current tool window toolbar	SHIFT+ALT	No equivalent
Move to the next tool window	ALT+F6	ALT+F6
Move to the previously selected window	SHIFT+ALT+F6	SHIFT+ALT+F6
Opens a new query editor window	No equivalent	CTRL+O
Display Object Explorer	F8	F8
Display Registered Servers	CTRL+ALT+R	No equivalent
Display Template Explorer	CTRL+ALT+T	CTRL+ALT+T
Display Solution Explorer	CTRL+ALT+L	CTRL+ALT+L
Display the Summary Window	F7	F7
Display the Properties Window	F4	F4
Display the Toolbox	CTRL+ALT+X	CTRL+ALT+X
Display the Bookmarks Window	CTRL+K, CTRL+W	No equivalent
Display the Browser Window	CTRL_ALT+R	CTRL_ALT+R

Text Selection Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Select text from the cursor to the beginning of the document	CTRL+SHIFT+ HOME	CTRL+SHIFT+ HOME
Select text from the cursor to the end of the document	CTRL+SHIFT+END	CTRL+SHIFT+END
Select text from the cursor to the start of the current line	SHIFT+HOME	SHIFT+HOME
Select text from the cursor to the end of the current line	SHIFT+END	SHIFT+END
Select text down line by line starting from the cursor	SHIFT+ DOWN ARROW	SHIFT+ DOWN ARROW
Select text up line by line starting from the cursor	SHIFT+UP ARROW	SHIFT+UP ARROW
Move the cursor up one line, extending the selection	SHIFT+ALT+ UP ARROW	SHIFT+ALT+ UP ARROW
Extend selection up one page	SHIFT+ PAGE UP	SHIFT+ PAGE UP
Extend selection down one page	SHIFT+ PAGE DOWN	SHIFT+ PAGE DOWN
Select the entire current document	CTRL+A	CTRL+A
Select the word containing the cursor, or the closest word	CTRL+W	CTRL+W
Select the current location in the editor, back to the previous location in the editor	CTRL+=	CTRL+=
Extend the selection to the top of the current window	CTRL+SHIFT+ PAGE UP	CTRL+SHIFT+ PAGE UP
Move the cursor to the last line in view, extending the selection	CTRL+SHIFT+ PAGE DOWN	CTRL+SHIFT+ PAGE DOWN
Extend the selection one word to the right	CTRL+SHIFT+ RIGHT ARROW	CTRL+SHIFT+ RIGHT ARROW
Extend the selection one word to the left	CTRL+SHIFT+ LEFT ARROW	CTRL+SHIFT+ LEFT ARROW
Move the cursor to the right one word, extending the selection	CTRL+SHIFT+ALT+ RIGHT ARROW	CTRL+SHIFT+ALT+ RIGHT ARROW
Move the cursor to the left one word, extending the selection	CTRL+SHIFT+ALT+ LEFT ARROW	CTRL+SHIFT+ALT+ LEFT ARROW

Text Manipulation in Code Editor Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Insert a new line	ENTER or SHIFT+ENTER	ENTER or SHIFT+ENTER
Swap the characters on either side of the cursor	CTRL+T	No equivalent
Delete on character to the right of the cursor	DELETE	DELETE
Delete one character to the left of the cursor	BACKSPACE or SHIFT+ BACKSPACE	BACKSPACE or SHIFT+ BACKSPACE
Insert the number of spaces configured for the editor	TAB	TAB
Insert a blank line above the cursor	CTRL+ENTER	CTRL+ENTER
Insert a blank line below the cursor	CTRL+SHIFT+ ENTER	CTRL+SHIFT+ ENTER
Change the selected text to lowercase	CTRL+SHIFT+L	CTRL+SHIFT+L
Change the selected text to uppercase	CTRL+SHIFT+U	CTRL+SHIFT+U
Toggle between insertion mode and overtype mode	INSERT	INSERT
Move selected lines to the left on tab stop	SHIFT+TAB	SHIFT+TAB
Delete the word to the right of the cursor	CTRL+DELETE	CTRL+DELETE
Delete the word to the left of the cursor	CTRL+BACKSPACE	CTRL+BACKSPACE
Transpose the words on either side of the cursor	CTRL+SHIFT+T	CTRL+SHIFT+T

Search Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Display the Find dialog box	CTRL+F	CTRL+F
Display the Replace dialog box	CTRL+H	CTRL+H
Start incremental search. Type the characters to search for or press CTRL+I to search for characters from the previous search	CTRL+I	CTRL+B
Find the next occurrence of the previous search text	F3	F3
Find the previous occurrence of the search text	SHIFT+F3	SHIFT+F3
Find the next occurrence of the currently selected text	CTRL+F3	CTRL+F3
Find the previous occurrence of the currently selected text	CTRL+SHIFT+F3	CTRL+SHIFT+F3
Display the Replace in Files dialog box	CTRL+SHIFT+H	CTRL+SHIFT+H
Reverse incremental search so it starts at the bottom of the file and searches to the top	CTRL+SHIFT+I	CTRL+SHIFT+I
Select or clear the Search up option in Find and Replace	ALT+F3, B	ALT+F3, B
Stop the Find in Files search	ALT+F3, S	ALT+F3, S
Select or clear the Find whole word option in Find and Replace	ALT+F3, W	ALT+F3, W
Selects or clears the Wildcard option in Find and Replace	ALT+F3, P	ALT+F3, P

Activity Monitor Keyboard Shortcuts

Action	Standard	Sql Server 2000/2005
Refresh	F5	F5
Filter the monitor display	CTRL+SHIFT+F	CTRL+SHIFT+F