Mark B. – tech

SQL Server Hidden Features

Here some hidden feature of SQL Server. For example, undocumented system stored procedures, tricks to do things which are very useful but not documented enough.


Stored Procedures

  • sp_msforeachtable: Runs a command with ‘?’ replaced with each table name (v6.5 and up)
  • sp_msforeachdb: Runs a command with ‘?’ replaced with each database name (v7 and up)
  • sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
  • sp_helptext: If you want the code of a stored procedure, view & UDF
  • sp_tables: return a list of all tables and views of database in scope.
  • sp_stored_procedures: return a list of all stored procedures
  • xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
  • xp_fixeddrives:: Find the fixed drive with largest free space
  • sp_help: If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1

Snippets

  • Returning rows in random order
  • All database User Objects by Last Modified Date
  • Return Date Only
  • Find records which date falls somewhere inside the current week.
  • Find records which date occurred last week.
  • Returns the date for the beginning of the current week.
  • Returns the date for the beginning of last week.
  • See the text of a procedure that has been deployed to a server
  • Drop all connections to the database
  • Table Checksum
  • Row Checksum
  • Drop all the procedures in a database
  • Re-map the login Ids correctly after restore
  • Call Stored Procedures from an INSERT statement
  • Find Procedures By Keyword
  • Drop all the procedures in a database
  • Query the transaction log for a database programmatically.

Functions

  • HashBytes()
  • EncryptByKey
  • PIVOT command

Misc

  • Connection String extras
  • TableDiff.exe
  • Triggers for Logon Events (New in Service Pack 2)
  • Boosting performance with persisted-computed-columns (pcc).
  • DEFAULT_SCHEMA setting in sys.database_principles
  • Forced Parameterization
  • Vardecimal Storage Format
  • Figuring out the most popular queries in seconds
  • Scalable Shared Databases
  • Table/Stored Procedure Filter feature in SQL Management Studio
  • Trace flags
  • Number after a GO repeats the batch
  • Security using schemas
  • Encryption using built in encryption functions, views and base tables with triggers
Advertisements
This entry was published on March 27, 2012 at 7:36 am. It’s filed under IT - Tutorial, Tutorial and tagged , . Bookmark the permalink. Follow any comments here with the RSS feed for this post.

One thought on “SQL Server Hidden Features

  1. I am now not certain the place you’re getting your info, but great topic. I needs to spend a while learning much more or understanding more. Thank you for excellent information I used to be searching for this information for my mission.

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

%d bloggers like this: