CODEWITHSUNDEEP
sqlsql-server
Rhett Claypool
Rhett Claypool

Reputation: 65

How to run SQL statements via SQL Server Management Studio studio against your database?

We have a SQL Server instance hosted offsite and we do not have access to the actual server environment. We needed to try and speed up the server's responsiveness and their helpdesk sent us this:

Run the following via SQL via SQL Server Management Studio against your iMIS database:

SELECT 
    OBJECT_NAME(parent_id) AS TableName,
    name AS TriggerName,
    create_date AS CreationDate,
    modify_date AS ModifyDate
FROM 
    sys.triggers

Questions: Wouldn't we have to have access to the SQL Server in order to run that code? Does that code make sense? Would it speed up our database?

Upvotes: 1

Views: 74

Answers (3)

Adil B
Adil B

Reputation: 16806

You could use Powershell to perform this query, and thereby avoid having to use or install SQL Server Management Studio altogether:

$connection = New-Object System.Data.SqlClient.SQLConnection("Data Source=<DB Hostname>;User ID=<DB Username>; Password=<DB Password>")
$connection.Open()
$cmd = New-Object System.Data.SqlClient.SqlCommand("SELECT OBJECT_NAME(parent_id) AS TableName, name AS TriggerName, create_date AS CreationDate, modify_date AS ModifyDate FROM sys.triggers",$connection)
$sqlReader = $cmd.ExecuteReader()
while ($sqlReader.Read()) { $sqlReader["TableName"] + ", " + $sqlReader["TriggerName"] + ", " + $sqlReader["CreationDate"] + ", " + $sqlReader["ModifyDate"]}
$connection.Close()

This code won't speed up your database, but will give helpdesk some information about the triggers set on your database. They'll probably use that information for further troubleshooting.

See this page for more information.

Upvotes: 2

Kashyap MNVL
Kashyap MNVL

Reputation: 603

You just need to be able to connect to the database server from your local instance of SSMS. You just need to put in the "Server Name" and Authenticate yourself to the server before you can run this query.

You might need proper permissions to be able to run these statements. But you can give it a try.

The query above gets the Triggers information ( TableName: The table name on which the trigger is created. TriggerName: The name of the trigger created on the above table. CreationDate: The datetime this trigger was created ModifyDate: The datetime this trigger was last modified. ) Triggers might slow down performance and response times if the Audit table to which the trigger writes data is very huge.

Upvotes: 3

The Impaler
The Impaler

Reputation: 48780

That's a simple SQL statement. No need to access the box itself at the OS level. Just connect like you do from your application (or using your favorite tool "sql mgmt studio?") and run that SQL.

The only caveat I see, is that you need permissions to those object. Do you have them? If not sure, well... run it and see if it allows you to do it.

And, no. That query per se will not speed up anything. Maybe those guys need to see the result from the query to do something else. This looks like an initial step for a solution.

Upvotes: 1

Related Questions