Tuesday, August 25, 2009

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)

Monday, August 24, 2009

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

Wednesday, August 19, 2009

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

Tuesday, August 11, 2009

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