Some scripts

Script :: To compare objects in two databases.

    DECLARE @Sourcedb sysname
    DECLARE @Destdb sysname
    DECLARE @SQL varchar(max)
    
    SELECT @Sourcedb = '<<Database1>>'
    SELECT @Destdb = '<<Database2>>'
    
    SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name''
                         , CASE 
                           WHEN SoSource.object_id IS NULL      THEN SoDestination.type_desc +  '' missing in the source -- ' 
                                                                                             + @Sourcedb + ''' COLLATE database_default
                           WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc      +  '' missing in the Destination -- ' + @Destdb 
                                                                                             + ''' COLLATE database_default
                           ELSE SoDestination.type_desc + '' available in both Source and Destination'' COLLATE database_default
                           END ''Status''
                     FROM (SELECT * FROM ' + @Sourcedb + '.SYS.objects 
                            WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource 
          FULL OUTER JOIN (SELECT * FROM ' + @Destdb + '.SYS.objects 
                            WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination
                       ON SoSource.name = SoDestination.name COLLATE database_default
                      AND SoSource.type = SoDestination.type COLLATE database_default
                      ORDER BY isnull(SoSource.type,SoDestination.type)'
     EXEC (@Sql)

------------------------------------------------------------------------------------------------------------------------------------------------------

Script :: To get SQL transaction locks details.

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,
        AT.name as TransactionName,
        CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()

ORDER BY L.request_session_id

------------------------------------------------------------------------------------------------------------------------------------------------------

Script :: Find a text in database.

CREATE PROCEDURE [REWARDS].[p_FindTextInObjects]

declare 

  @findText               nvarchar(1000),

  @havingCountGreaterThan int

set @findText = '#GLEntry'

set @havingCountGreaterThan=0

  declare @findText2 nvarchar(1002)

--  Example Calls

--    exec p_FindTextInObjects 'union all', 1

--    exec p_FindTextInObjects 'XML EXPLICIT'

--    exec p_FindTextInObjects 'declare%cursor', 1

--    exec p_FindTextInObjects '@@IDENTITY', 1

--    exec p_FindTextInObjects 'summit'

--    exec p_FindTextInObjects 'vehicle'

  if @findText is null 

    return

  

  set transaction isolation level read UNCOMMITTED

  create table #output

  ( ObjectType varchar(12),

    Name       varchar(128),

    ColumnType varchar(30),

    Length     int,

    Nullable   varchar(10),

    FindCount  int, 

    ObjectId   int

  )

  set @findText2 = '%' + upper(@findText) + '%'

  

-- tables

  insert into #output (ObjectType, Name)

  select

    case TYPE

      when 'U'  then 'Table'

      when 'F'  then 'Foreign Key'

      else TYPE

    end ObjectType,

    so.name Name

  from sysobjects so

  where so.name like @findText2

  and type in ('U','F')

  order by 1

-- columns

  insert into #output (ObjectType, Name, ColumnType, Length, Nullable)

  select

    'Column' ObjectType,

    convert(varchar(128), so.name + '.'+ sc.name) Name,

    convert(varchar(30),st.name) ColumnType,

    sc.length,

    case sc.isnullable when 0 then 'not null' else 'null' end [Null]

  from syscolumns sc

  join systypes st on st.xusertype = sc.xusertype

  join sysobjects so on sc.id = so.id

  where sc.name like @findText2

  order by 2,3

-- indexes

  insert into #output (ObjectType, Name)

  select

    'Index' ObjectType,

    si.name Name

  from sysindexes si

  where si.name like @findText2

  order by 1

-- procs, triggers,

  insert into #output (ObjectType, Name, FindCount, ObjectId)

  select

    case TYPE

      when 'P'  then 'Procedure'

      when 'TR' then 'Trigger'

      when 'U'  then 'Table'

      when 'V'  then 'View'

      else TYPE

    end ObjectType,

    OBJECT_NAME(sm.ID) as Name,

    count(*) as [FindCount],

    sm.ID

  from SYSCOMMENTS sm

  join SYSOBJECTS so on so.ID = sm.ID

  where OBJECTPROPERTY(sm.ID, 'IsMSShipped') = 0

  and PATINDEX(@findText2, upper([TEXT])) > 0

  group by sm.ID, TYPE

  having (count(*) > @havingCountGreaterThan)

  order by TYPE, [Name]

  select * from #output

  drop table #output

  set transaction isolation level read COMMITTED

  

  return

GO

------------------------------------------------------------------------------------------------------------------------------------------------------

Script :: Database buffer status

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value

FROM sys.dm_os_performance_counters 

WHERE RTRIM([object_name]) LIKE '%Buffer Manager'

AND counter_name = 'Database Pages';

;WITH src AS

(

SELECT 

database_id, db_buffer_pages = COUNT_BIG(*)

FROM sys.dm_os_buffer_descriptors

--WHERE database_id BETWEEN 5 AND 32766

GROUP BY database_id

)

SELECT

[db_name] = CASE [database_id] WHEN 32767 

THEN 'Resource DB' 

ELSE DB_NAME([database_id]) END,

db_buffer_pages,

db_buffer_MB = db_buffer_pages / 128,

db_buffer_percent = CONVERT(DECIMAL(6,3), 

db_buffer_pages * 100.0 / @total_buffer)

FROM src

ORDER BY db_buffer_MB DESC; 

------------------------------------------------------------------------------------------------------------------------------------------------------

Script :: Database text search.

DECLARE @SearchStr nvarchar(100) = 'Corporate Web Site'

DECLARE @Results TABLE (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

------------------------------------------------------------------------------------------------------------------------------------------------------