Freigeben über

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

  • Artikel

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, @pos-1)

      -- 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

  • Anonymous
    October 27, 2006
    PingBack from http://www.frogameleon.com/blog/sysk-228-get-table-columns-or-rows-with-single-key-press

  • Anonymous
    October 29, 2006
    I am a regular vistor of Irena Kennedy's blog 'Something You Should Know" She knows to make the tools...

  • Anonymous
    October 30, 2006
    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

  • Anonymous
    October 06, 2007
    Irena Kennedy's blog 'Something You Should Know" She knows to make the tools...

  • Anonymous
    December 22, 2008
    Great! Thx. Is it somehow possible to select the table in the Object Explorer and then use your shortcuts? THX Peter

  • Anonymous
    June 24, 2009
    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.

  • Anonymous
    September 02, 2009
    pretty helpful keyboard shortcuts for SSMS..  I am glad I stumbled across here. Thanks.