I have a lot of views and stored procedures I periodically need to review, and in order to do so, I paste the TSQL from SQL Server into an Excel spreadsheet where I can create formulas to analyse it.
Is there a way (using VBA?) to import the actual script from a defined view or stored procedure into a cell in Excel?
So just to confirm, I want to return the script, and not the results of executing the script.
Many Thanks
From an SQL point of view you can get the view text using this query:
SELECT [definition]
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON m.object_id=o.object_id
WHERE [name] = 'ViewName'
AND [type] = 'V'
for procs change the WHERE to:
WHERE [name] = 'Procname'
AND [Type] = 'P'
Edit: here is a quick VBA function to return this data, just replace the connection details:
Public Function GetObject(ByVal nm As String, ByVal Tp As String) As String
Set objMyConn = New ADODB.Connection
Set objMyRecordset = New ADODB.Recordset
Dim strSQL As String
Dim retval As String
With objMyConn
.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=[MyID];Password=[MyPass];Data Source=[ServerName];" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;" & _
"Tag with column collation when possible=False;Initial Catalog=[DBName]"
.Open
End With
strSQL = "SELECT [definition] " & _
"FROM sys.sql_modules m " & _
"INNER JOIN sys.objects o ON m.object_id=o.object_id " & _
"WHERE [name] = '" & nm & _
"' AND [Type] = '" & Tp & "'"
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open strSQL
Do While Not objMyRecordset.EOF
retval = objMyRecordset.Fields("description").Value
objMyRecordset.MoveNext
Loop
Set GetObject = retval
End Function
objMyRecordset.Open line and check the value of strSQL, run this value manually against the DB to make sure it returns the right info.You might also find the exec sp_helptext useful for this as it will split the output into seperate rows instead of one column, which might make it easier to view in excel.
exec sp_helptext "YourView/SP"
You can get the "code" (the T-SQL definition) for your stored procedures from SQL Server by inspecting the sys.procedures and sys.sql_modules catalog views:
SELECT
[pr].[name] ,
[pr].[object_id] ,
[pr].[type_desc] ,
[pr].[create_date] ,
[pr].[modify_date] ,
[m].[definition] ,
[m].[uses_ansi_nulls] ,
[m].[uses_quoted_identifier] ,
[m].[is_schema_bound]
FROM sys.[procedures] pr
INNER JOIN sys.[sql_modules] m ON [pr].[object_id] = [m].[object_id]
WHERE pr.Name = 'whatever_stored_proc_name_you_want_to_analyze'
Same goes for views:
SELECT
v.[name] ,
v.[object_id] ,
v.[type_desc] ,
v.[create_date] ,
v.[modify_date] ,
[m].[definition] ,
[m].[uses_ansi_nulls] ,
[m].[uses_quoted_identifier] ,
[m].[is_schema_bound]
FROM sys.views v
INNER JOIN sys.[sql_modules] m ON v.[object_id] = [m].[object_id]
WHERE v.Name = 'whatever_view_you_want_to_analyze'