mark a table or view in a t-sql script, and open/browse this table/view via hotkey
-
Hi,
is there a way to mark a table or view in a t-sql script, and open/browse this table/view via hotkey ?
maybe 3d-party-tool ... etc.
Thanks
Regards
Nicole
-
yes, via keyboard shortcuts.
i take advantage of this so much, it's crazy. i have shortcuts to script the definition of the hightlighted object, select top 100 fromt hat object, and so much more.
here's a specific example:
create this stored procedure in master: note i'm marking it as a system object as well:
IF OBJECT_ID('[dbo].[sp_show]') IS NOT NULL
DROP PROCEDURE [dbo].[sp_show]
GO
--#################################################################################################
--developer utility function added by Lowell, used in SQL Server Management Studio
--Purpose: Preview top 100 rows of a given table
--additional modification: fast count of rows if a TABLE or #Temp (no results on views)
--#################################################################################################
CREATE PROCEDURE sp_show
--USAGE: sp_show gmact
@TblName VARCHAR(128),
@Top INT = 100
--WITH ENCRYPTION
AS
BEGIN
DECLARE @cmd VARCHAR(MAX)
IF LEFT(@TblName,1) = '#'
BEGIN
SELECT
ps.row_count AS TotalRows
FROM tempdb.sys.indexes AS i
INNER JOIN tempdb.sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN tempdb.sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID('tempdb.dbo.' + RTRIM(@TblName)) ;
END
ELSE
BEGIN
SELECT
ps.row_count AS TotalRows
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ps
ON i.OBJECT_ID = ps.OBJECT_ID
AND i.index_id = ps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
AND o.object_id = OBJECT_ID(RTRIM(@TblName)) ;
END
SET @cmd = 'SELECT TOP ' + CONVERT(VARCHAR,@Top) + ' * FROM ' + QUOTENAME(@TblName) + ' ORDER BY 1 DESC '
EXEC(@cmd)
END
GO
--#################################################################################################
--Mark as a system object
EXECUTE sp_ms_marksystemobject 'sp_show'
GRANT EXECUTE ON dbo.sp_show TO PUBLIC;
--#################################################################################################
Next, In SSMS, go to Tools>>Options>>Keyboard>>Query Shortcuts
following my example, under Ctrl+6, just copy the name of the procedure sp_show
now, in a new tab, open up a script: highlight a table or viewname and click control+6
SSMS will execute the procedure with the highlighted string as the parameter, and effectively give you a rowcount of the table, as well as a preview of the top 100 rows.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
🙂 cool, thank you :w00t:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply