SYSK 228: Get Table Columns or Rows with Single Key Press


Imagine this:  you type in a table name in SQL Server Management Studio, press Ctrl+9 and get all rows from that table displayed in the results pane.  Or you press Ctrl+8 and instead of data rows, you get metadata about columns of that table. 


 


No more typing of ‘SELECT * FROM MyTable’ over and over again…  And for columns, no more needing to change the database – just specify the database name prior to table name, e.g. ‘AdventureWorks.HumanResources.Shift’.


 


Here is what you need to do to make this happen:


 


1.  Create two stored procedures (script below) in master database


 


USE master;


 


CREATE PROC GetRows(@TableName sysname, @RowCount int = 1)


AS


    SET NOCOUNT ON;


 


      declare @sql varchar(512)


    if @RowCount > 1


            set @sql =  ‘SELECT TOP ‘ + STR(@RowCount) + ‘ * FROM ‘


      else


            set @sql =  ‘SELECT * FROM ‘


      set @sql =  @sql + replace(quotename(@tablename), ‘.’, ‘].[‘)


      EXEC (@sql)


GO


 


 


CREATE PROC GetColumns(@TableName sysname)


AS


    SET NOCOUNT ON;


 


      — column info


      declare @sql nvarchar(512)


 


      — get db name


      declare @dbname nvarchar(256)


      set @dbname = @tablename


 


      declare @pos int


      set @pos = charindex(‘.’, @tablename)


      if @pos is not null AND @pos > 0


            set @dbname = left(@tablename, @pos1)


 


      — did we get database name?


      declare @dbid int


      set @sql = N‘select @dbid_out = db_id(‘ + quotename(@dbname, ””) + ‘)’


      EXEC sp_executesql @sql, N‘@dbid_out int OUT’, @dbid_out=@dbid OUTPUT;


 


      if @dbid > 0


            set @sql = ‘USE ‘ + quotename(@dbname) + ‘;’


      else


            set @sql = ;


 


      set @sql =  @sql + ‘SELECT sys.columns.name as name, column_id, sys.types.name as type, sys.columns.max_length, sys.columns.precision, sys.columns.scale, sys.columns.is_nullable, is_identity, is_computed FROM sys.columns inner join sys.types on sys.columns.system_type_id = sys.types.system_type_id where object_id = object_id(”’ + replace(quotename(@tablename), ‘.’, ‘].[‘) + ”’)’


      EXEC (@sql)


 


GO


 


2.  Create a keyboard accelerator for a stored procedure


1.         On the Tools menu, click Options.


2.         On the Keyboard tab page, select an unused keyboard combination in the Shortcut list.


3.         In the Stored Procedure box, type the stored procedure name – GetRows — and then click OK.


4.         Repeat steps 2 and 3 for the GetColumns stored procedure


 


3.   In the Query window of the SQL Server Management Studio, type in a table name in single quotes.  Optionally, add comma and number of top rows to select.  For example, to select top 1 row from AdventureWorks database HumanResource.Shift table, type in:


‘AdventureWorks.HumanResources.Shift’, 1


Now, select that line and press the shortcut you associated with the GetRows stored procedure.   Or, select just the ‘database.table’ name and press the shortcut associated with the GetColumns procedure.  You should get the expected results in the results pane below…


 


Note:  I had to close SQL Server Management Studio and re-open it after assigning shortcuts before it worked for me…


 


Hope you find use for this tip in your daily work life.


 

Comments (7)

  1. .NET says:

    I am a regular vistor of Irena Kennedy’s blog ‘Something You Should Know" She knows to make the tools…

  2. Irena Kennedy has a neat trick for SQL Server Management Studio to quickly display some rows from a table, or get the metadata on columns of a table: a keyboard shortcut that is fast and works in any database. Has anyone else created similar keyboard

  3. markovich says:

    Irena Kennedy’s blog ‘Something You Should Know" She knows to make the tools…

  4. Peter Gfader says:

    Great!

    Thx.

    Is it somehow possible to select the table in the Object Explorer and then use your shortcuts?

    THX

    Peter

  5. An Phu says:

    The drawback to this is you need to copy these two SP to every database server you have.

    I just map sp_helptext to Ctrl+F1.  (This will give me

    * table metadata if a table is selected.

    * view definition if a view is selected.

    * SP text if a SP is selected.

    I assign "select top 5 * from" to Ctrl+3.  This gives me the same functionality as your GetRows SP.  (Most of the time you don’t need all the rows returned.)

    These keyboard bindings will work on any database server without you having to deploy stuff.

  6. RK says:

    pretty helpful keyboard shortcuts for SSMS..  I am glad I stumbled across here.

    Thanks.