sp_ctrl3

Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.

Source: https://github.com/sqlsunday/sp_ctrl3

Installing it

You can place the object in the master database to make it available as a global shortcut on the instance. The procedure can also be put in a single user database if you only plan to use it there.

For ease of use, add a keyboard shortcut in SSMS:

Go to "Tools" -> "Options"
Under "Environment" -> "Keyboard" -> "Query Shortcuts"
Enter the text "sp_ctrl3" under any keyboard shortcut you like

Keyboard shortcuts will only work in new query windows, so you'll have to open a new one to use the shortcut.

What it does

Display detailed database object information

Most of the data displayed is formatted in a way that facilitates copying and pasting directly as a T-SQL statement like CREATE TABLE.

Object-level information

Object schema, name and type
Data space (filegroup or partition scheme)
Options (is it system-versioned, any ANSI options we need to know about)
Change tracking
Row count and allocated size on disk
Description from extended properties

Columns/parameters

Name and datatype of the column/parameter
Defaults, identity column definition
Collation, if not database default
Whether or not the column is nullable
Any non-standard ANSI options
If this column is constrained by a foreign key
Description from extended properties

Indexes and primary key/unique constraints

Type, name and list of columns
Included columns
Filter expression
Scripting options used
Data space assigned (filegroup or partition scheme)
Number of filtered rows, if filtering is applied

Foreign key constraints

Foreign key constraints to other objects
Foreign keys constraints on other objects that reference this object
Options used to create the constraint, like if it's disabled, etc.

Security policies

Name
Predicate and definition
Scripting options

Triggers

List of triggers and some information on them

Permissions

Code or object dependencies from/to this object

A simple ASCII graph that shows you other objects that reference this object, or are referenced by this object. This could be views, functions, etc. I've made an attempt at showing if it's a one-to-many, one-to-one, or many-to-one relationship.

Storage

Clustered/non-clustered indexes and/or heaps
How the index is stored (what partitions, what compression settings, etc)
Partition scheme, partition function, as well as the boundaries of each partition
Fill factor, row count of each partition
Reserved and used space on- and off-row, as well as a computed average row width
Number of open, closed and compressed columnstore segments

Code preview

A short preview of the first couple of rows. Handy for instance if your company keeps a description header in each object.

Plaintext schema search

You can search for any string used in the database schema, including

T-SQL code used in modules (procedures, triggers, views, functions)
Tables and columns
Constraints
Indexes and filter definitions
Schemas
Descriptions from extended properties

The search string can include any valid T-SQL wildcard expression. The output includes

The object schema, object type and name
Row count, if it's a table or an index
Line count for T-SQL modules
Line numbers of the matching results
Object description from extended properties

DISCLAIMER: This script may not be suitable to run in a production environment. I cannot assume any responsibility regarding the accuracy of the output information, performance impacts on your server, or any other consequence. If your jurisdiction does not allow for this kind of waiver/disclaimer, or if you do not accept these terms, you are NOT allowed to store, distribute or use this code in any way.

USAGE:

EXECUTE sp_ctrl3 {object name}

Blog post: https://sqlsunday.com/2016/11/28/sp_ctrl3/

SHORTCUT: In SQL Server Management Studio, go to Tools -> Options -> Environment -> Keyboard -> Query Shortcuts.

On a shortcut location of your choice, enter the following code, with the trailing space, without the quotes: "EXECUTE sp_ctrl3 ". To use, highlight the name of an object and press that keyboard shortcut. You may have to open a new query for the change to take effect. Also, objects denoted by schema (with a dot) need to be enclosed in quotes for this to work in older versions of SSMS.

sqlsunday/sp_ctrl3

ErrorLooks like something went wrong!

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Error
Looks like something went wrong!

Packages