Tag: SQL Server

How to View Stored Procedure Code in SQL Server

The most common way to view the code of a stored procedure in SSMS is doing a right-click on the stored procedure and select Script Stored Procedure as and select CREATE To.

But this is not practical when you have several stored procedures to look at.

Another way to do it by using a script:

USE [DATABASENAME]
GO

EXEC sp_helptext STOREDPROCEDURENAME
GO

You might want to set the Results to Text for easy copy and paste for further editing.

How to Find a String in SQL Server Stored Procedures

This is just quick tip found from this link and is actually good to use as a starting point:

USE DATABASE;
GO

SELECT [Schema] = schema_name(o.schema_id), o.Name, o.type
FROM sys.sql_modules m
INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition LIKE '%YOUR-STRING%'
GO

How to Search for a String in All Tables in a Database

If you want to look where a string is in all tables in a database, how do you do that?

Here’s a very helpful SQL to run:

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Updated and tested by Tim Gaunt
-- http://www.thesitedoctor.co.uk
-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
-- Date modified: 03rd March 2011 19:00 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL

BEGIN
  SET @ColumnName = ''
  SET @TableName = 
  (
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(
      OBJECT_ID(
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
      ), 'IsMSShipped'
    ) = 0
  )

  WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
         
  BEGIN
    SET @ColumnName =
    (
      SELECT MIN(QUOTENAME(COLUMN_NAME))
      FRO M INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
        AND TABLE_NAME = PARSENAME(@TableName, 1)
        AND DATA_TYPE IN ('char', 'varchar', 'nchar'
          , 'nvarchar', 'int', 'decimal')
        AND QUOTENAME(COLUMN_NAME) > @ColumnName
    )
 
    IF @ColumnName IS NOT NULL
         
    BEGIN
      INSERT INTO #Results
      EXEC
      (
        'SELECT ''' + @TableName + '.' + @ColumnName + ''', 
        LEFT(' + @ColumnName + ', 3630) 
        FROM ' + @TableName + ' (NOLOCK) ' +
        ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
      )
    END
  END   
END
 
SELECT ColumnName, ColumnValue FROM #Results
 
DROP TABLE #Results

Format SQL Using Redgate SQL Prompt

As a follow-up of the post String Literals as Column Aliases are Deprecated, this one feature from Redgate’s SQL Prompt is quite handy to correct any existing SQL scripts quickly.

Using the SQL from How to Find All References to an Object in a SQL Server Database as an example, here’s what we originally have:

SELECT DISTINCT OBJECT_NAME(m.object_id) AS 'object name', m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%Customer_Name%'

Pay attention to the object name within the single quotes as a column alias.

If this is only a one-off such as this one, it’s no sweat to fix it manually, but what happens if you have a long query with multiple lines of column aliases that you want to fix?

If you have Redgate SQL Prompt, you just need to set it once in the Options as shown below:

In SSMS, go to SQL Prompt on the top menu, and select Options.
In the Options window, go to Format, Styles. Under Actions, check Apply column alias style and select column AS “alias”, then click OK.

You’re done with the configuration.

How do you use it?

The best way to use it is by using a keyboard shortcut.

If you click on the SQL Prompt from the menu again, you’ll see an option for Format SQL with a shortcut CTRL+K, CTRL+Y.

In your query window where you have the SQL you want to fix, just use the keyboard shortcut (or you can do that from the menu as well) and right away, it will reformat the query with the fix:

SELECT DISTINCT
       OBJECT_NAME(m.object_id) AS "object name",
       m.*
FROM sys.sql_modules m
WHERE m.definition LIKE '%Customer_Name%';

This is one of the useful features in SQL Prompt to make your life easier. If you notice from the screenshots above, the top bar also has a red color to indicate that it is a production database. It’s a nice way to color code different database environment that can also be configured in SQL Prompt.

Download

Redgate SQL Prompt (FREE Trial)

String Literals as Column Aliases are Deprecated

Something that’s still found in SQL scripts in enterprise environment, even with newly written scripts is the use of string literals as column aliases.

SELECT record_id AS ID, account_number AS 'Account Number'
FROM RequestTable

Although this feature has been deprecated since 2012, old habits die hard.

So what’s the right way to write it?

You can either use alias without a whitespace or use a double quote character. Either of these examples below is acceptable.

SELECT record_id AS ID, account_number AS Account_Number
FROM RequestTable

SELECT record_id AS ID, account_number AS "Account Number"
FROM RequestTable

You can read more about this deprecated feature from the links below.

How to View Stored Procedure Code in SQL Server

Further Reading

How to Find a String in SQL Server Stored Procedures

Further Reading

How to Search for a String in All Tables in a Database

Further Reading

Format SQL Using Redgate SQL Prompt

How do you use it?

Further Reading

Download

String Literals as Column Aliases are Deprecated

Further Reading

Recent Posts

Further Reading

Further Reading

Further Reading

How do you use it?

Further Reading

Download

Further Reading

Recent Posts

Tags