Category: How To

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 Remove Cached Credentials without Rebooting Windows

The typical situation is you’ve connected to a network share with a User ID and a password. Then for one reason or another, the password was changed.

As soon as you get asked to enter the User ID and password again, even after entering the latest password, you’re still getting an error message:

Open Folder

\\server_name\c$ is not accessible. You might not have permission to use this network resource. Contact the administrator of this server to find out if you have access permissions.

The issue is after the password was recycled, the old password is still cached in your system, so we need to clear out the cache first.

You can check on what’s in the cache by opening a PowerShell or Command Prompt and type in the following:

C:\>net use

And you should see something similar to this:

C:\>net use
New connections will be remembered.


Status  Local    Remote                    Network
----------------------------------------------------------------------
OK      G:       \\Server1\Apps            Microsoft Windows Network
OK      H:       \\Server2\Customers       Microsoft Windows Network

Disconnected     \\server_name\c$          Microsoft Windows Network
The command completed successfully.

The network drive you’re looking for is the one that is disconnected and we need to delete it.

We can do so by typing the following command:

net use \\server_name\c$ /delete

And you will get confirmation that it was deleted successfully.

C:\>net use \\server_name\c$ /delete
\\server_name\c$ was deleted successfully.

Once you see this, you can close the PowerShell or Command Prompt windows and try to reconnect to the network share drive again.

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

How to Install Cryptomator on macOS

Cryptomator is a free and open-source client-side encryption tool that can be used to encrypt files before uploading them to cloud storage services.

Here are the steps to install Cryptomator:

Go to the Cryptomator website.
Before you install the Cryptomator program, you can install macFUSE.
Please note that Cryptomator recommended macFuse for Intel and FUSE-T for Apple Silicon but I found that FUSE-T is still buggy. It is still tagged as experimental, so use it at your own risk. It gave me a panic attack when I saw less than half of my folders showed up after upgrading to a newer version of FUSE-T. Only after I switched back to macFUSE could I see the complete folders again.
Open the downloaded file and follow the installation wizard.
Once the installation is complete, launch Cryptomator.
Create a new vault by clicking on Create New Vault and selecting the location where you want to create it.
Set a password for the vault and click Create Vault.
Your new vault will now be visible in the Cryptomator interface. You can drag and drop files into it to encrypt them.

That’s it! You have successfully installed and set up Cryptomator.

If you find the software useful, you can help supporting it by purchasing a Supporter Certificate or Donation.

Download

Cryptomator: Download
macFUSE

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 Remove Cached Credentials without Rebooting Windows

Further Reading

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

Further Reading

How to Install Cryptomator on macOS

Download

Further Reading

Recent Posts

Further Reading

Further Reading

Further Reading

Further Reading

Download

Further Reading

Recent Posts

Tags