Sql Server Notes

List SPID and kill a process

exec sp_who2
kill <SPID>  -- when a query is too heavy and stops responding, killing it is a good idea

List all users

select * from master.sys.server_principals

Search for a function

SELECT OBJECT_NAME(sm.object_id) as name, sm.definition
FROM sys.sql_modules AS sm  
JOIN sys.objects AS o ON sm.object_id = o.object_id  
WHERE OBJECT_NAME(sm.object_id) like '%GetFxForwardsForBrokerRec%'

Search for a stored procedure

select specific_schema + '.' + routine_name as name, ROUTINE_DEFINITION as definition
from information_schema.routines 
where routine_type = 'PROCEDURE'
and definition like '%STR_TO_SEARCH%'
order by name

Show temp tables and their indexes

SELECT *
FROM tempdb.sys.sysobjects
WHERE name LIKE '%#test%'

Show definition of function

sp_helptext 'dbo.MyFunction'

Search for info in error log

EXEC master.dbo.xp_readerrorlog 0, 1, "string to search for", NULL, NULL, NULL, "desc"

Show Auth Scheme

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; 

List logins

SELECT name, type_desc, is_disabled
FROM sys.server_principals

Create a login

CREATE LOGIN login_name WITH PASSWORD = 'my password';  

Fix orphaned login

When edit the user mapping of a login, if SQL Server gives the following error:

User, group, or role 'someuser' already exists in the current database. (Microsoft SQL Server, Error: 15023)

then the login is an 'orphaned login', and need to be fixed.

The old way is to run the following command:

use MyDB;
exec sp_change_users_login 'Auto_Fix', '<login_name>'

but the above command is deprecated. The new way is as follows:

alter user <user_name> with Login = '<login_name>'

Show permissions on schemas

SELECT state_desc, permission_name, 'ON' AS prep, class_desc + '::' + SCHEMA_NAME(major_id) AS schema_name,
'TO' AS prep, USER_NAME(grantee_principal_id)
FROM sys.database_permissions AS Perm
JOIN sys.database_principals AS Prin
   ON Perm.major_ID = Prin.principal_id AND class_desc = 'SCHEMA'
WHERE major_id = SCHEMA_ID('my_dbo')

Show permissions on tables

sp_table_privileges 'table_name';  
-- note that it's the table name only, without schema. ie. you cannot write it as:
-- sp_table_privileges 'dbo.table_name';

Linked Servers

Another DB can be linked to the current DB as linked DB servers. These can be found in SQL Server Management Studio (Use SUPERMART as an example):

<DB> | Server Objects | Linked Servers | <LinkedDB>

If it doesn't work, it's possible that the linked server itself has some problems. It can be connected to diagnose the problem.

It can be deleted, and re-created.

If you get errors such as "The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "x" was unable to begin a distributed transaction" or "Error: 8501 MSDTC on server is unavailable", then you will have to start the following Windows process:

• Distributed Transaction Coordinator

Then under Admistrative Tools -> Component Services:

• Click on Console Root -> Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator : Right click, "New window here" | Local DTC | Right click, "Properties"
• Under the Security Tab: Enable Network DTC Access, Allow Remote Clients
• Under Transaction Manager Communication: Check Allow Inbound & Allow Outbound
• Clicking OK should then automatically restart the DTC service.

Stored Proc to Search All Tables (Don't use this, it might be very heavy---++!)

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
 
BEGIN
 
    -- 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))
                FROM     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
END

Add a new login

• Launch 'SQL Server Management Studio'
• in left pane, go to '<server> | Security | Logins', right-click on it
• choose 'New Login'
• fill in name, choose authentication method
• click 'User Mapping' in left pane, check databases to be used by this ID, and for each of the DB, check the permissions in the list below ('Database role membership for').

Debug with SQL Server Profiler

Turn on the SQL Server Profiler (File | New Trace), and run application that accesses the DB, then we can see what the application executed. It also helps to add a filter so that only the ones that we are interested in can be shown:

• File | Properties | Events Selection | Column Filters
• click on the column to be filtered on (usually it's 'Text Data'), enter a condition (such as 'Like', enter a pattern), and check 'Exclude rows that do not contain values'.