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, @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.