SSMS add button to run set SQl code

Question

SSMS add button to run set SQl code

mfink

SSCommitted

Points: 1632
More actions
May 20, 2010 at 11:21 am

#232680

I use the follow code to get counts of tables in databases
SELECT 'Table Name'=convert(char(45),t.TABLE_NAME),
'Start Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
AND t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
I run it many times on differant servers and databases
it there a way to put a menu item in SSMS to execute it.
External tools is looking for a cmd or something.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jjtetzlo SSCrazy Points: 2511 More actions · Answer 1

I've had a similar need in the past, and what works for me is mapping a command to a keyboard shortcut.

In SSMS, go to Tools > Options, and click on Keyboard (under Environment). There you can paste in your command to one of the shortcut combos there. It gives you space for a few of them.

Hope that helps.

- Jeff

Wesley Norton SSCrazy Points: 2827 More actions · Answer 2

If you grab SQL Prompt, you can use the snippets. Makes things really smooth.

If you are like me and can't seem to convince someone to pay for that software, I'd do what Jeff recommended. However, you will have to put your code into a stored procedure for it to run from the keyboard commands.

mfink SSCommitted Points: 1632 More actions · Answer 3

Jeff

Thanks for the Idea. The commands I need to run are above. They will not fit into the space.

This code counts the records in a database by table. SO I will need to run it on any given database I am in.

I tried making a stored proced in Master then pasting in the execute command. The problem I have is it runs counts on the master everytime.

Any hints/clues to what I am doing wrong would help.

Mark

jjtetzlo SSCrazy Points: 2511 More actions · Answer 4

Hmm.. I got your command to fit. Are you typing it in manually or are you copy/pasting it in?

Copy/paste should work... but the window doesn't let you scroll left-to-right, so it's kinda cludgy.

- Jeff

mfink SSCommitted Points: 1632 More actions · Answer 5

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.

is the error message I receive. The command runs in full