Search all databases for an object using sp_MSforeachdb

I often need to search an entire database for the existence of a database object.  Typically a table or view that is used across databases.

The script below uses sp_MSforeachdb (1):

       
use master
go

declare @sqlstmt varchar(1000);
select @sqlstmt = 
         'use [?]  select  ''[?]'' as [DBName],'
       + ' SCHEMA_NAME(o.schema_id ) as [schema_name], OBJECT_NAME(m.object_id) as [ObjectName]  /* ,m.[definition] */'
       + ' from sys.sql_modules as m ' 
       + ' inner join sys.all_objects as o on m.object_id = o.object_id ' 
       + ' where definition like ''%mydatabaseobject%''; '

select @sqlstmt;

exec sp_MSforeachdb @sqlstmt;

       
 

(1) There is a lot of debate about sp_MSforeachdb, along with some alternative procedures.
You can read about them here:

http://www.sqlservercentral.com/articles/sp_msforeachdb/117654/
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/http://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx

Script to auto-create a MERGE statement

With SQL Server, the MERGE statement is one of the most powerful commands available to a developer. With one statement, you can run a column by column comparison, issue updates, inserts and delete. All with one statement. The downside, the MERGE syntax is complex, and quite the hassle to assemble. That's where this page comes in to play. Below is a MERGE build script that will create a standard MERGE statement for two tables.

 INTERSECT properly handles NULLS 

One hassle with doing a row by row comparison is how to deal with NULL's. Perhaps you turned off ANSI_NULLS. Alternatively, you can use the INTERSECT command. And as part of the MERGE statement, the process is straight forward. All you need is the statement:

 WHEN MATCHED AND NOT EXISTS(SELECT s.* INTERSECT SELECT t.*)

Reference these for more info on INTERSECT

http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/
http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx

A Declared Primary Key

MERGE requires you to tell it what column, or columns to use to match the two tables.
The script below expects the target (or destination) table to have a declared primary key. If your table does not have a declared primary key, you'll have to build out the match manually.

SSMS has a limit of 8000 characters in 'results to text'

You'll need to adjust the SSMS settings so 'Results to Text' are set to 8000. Still, if your table has many columns, the total output could exceed 8000.  If it does, the output will be chunked up a bit, but still in the proper order.

Understand MERGE

Before using this script, make sure you understand just what MERGE is doing. 

       
/* 
-- SET RESULTS TO TEXT || Tools | Options | Results to Text | 8000 
 uses INTERSECT: for comparison, both INTERSECT EXCEPT, handle NULL as a value 
, which allows proper comparison (as if ANSI_NULLS OFF, which is deprecated )

Reference these articles:

http://sqlblog.com/blogs/paul_white/archive/2011/06/22/undocumented-query-plans-equality-comparisons.aspx
http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/

*/
set nocount on;
declare  @MySourceTableView   varchar(128)  = '[schema].[MyInventory_table]'  ;
declare  @MyDestinationTable  varchar(128) =  'MyOtherTable' ; 
-- supports with/ or w/o schema, with/ or w/o brackets
-----------------------------------------------------------------
-- standardize object values
-----------------------------------------------------------------
declare    @_source_TableView varchar(128),@_destination_Table varchar(128)
   ,@_source_schema    varchar(128),@_destination_schema varchar(128),@_dest_object_id int ;
 select @_source_TableView = OBJECT_NAME(OBJECT_ID(@MySourceTableView));
 select @_source_schema  = OBJECT_SCHEMA_NAME(OBJECT_ID(@MySourceTableView));
 select @_dest_object_id  = OBJECT_ID(@MyDestinationTable);
 select @_destination_Table  = OBJECT_NAME(OBJECT_ID(@MyDestinationTable));
 select @_destination_schema = OBJECT_SCHEMA_NAME(OBJECT_ID(@MyDestinationTable));

 select @_destination_schema, @_destination_Table,@_source_schema,@_source_TableView,@_dest_object_id;
 -- PK from @_destination_Table;
-----------------------------------------------------------------
-- process variables varchar(max) if exceeds 8000 character truncation
-----------------------------------------------------------------
-- PK format for MERGE statement:  t. = s. , ....
declare  @_PKsyslist table( COLUMN_NAME nvarchar(128), ORDINAL_POSITION int ) ;

declare  @colorder int = 0     ,@maxcol int = 0     
  ,@colname varchar(128) = ''   ,@colxtype int = 0 
  ,@Values varchar(max) = ''   ,@insert varchar(max) = ''   
  ,@update varchar(max) = ''   ,@openquery varchar(max) = '' 
  ,@_PKforMergeList varchar(max) = '' ,@_PKforCIDXList varchar(max) = ''
  ,@MergeSection varchar(max) = '' ,@Merge1Section varchar(max) = '' 
  ,@Merge1aSection varchar(max) = '' ,@Merge2Section varchar(max) = '' 
  ,@SPSection_cTwo varchar(max) = '' ,@_procedure_all varchar(max) = '' 
  ,@counter int = 0     ,@updatecolname varchar(max) = '';
declare  @debugflag        char(1) = 'x' ;  

---------------------------------------------------------------------------------------------------------
-- Get PK for MERGE statement:  t. = s. , - Separate to use ordinal position
---------------------------------------------------------------------------------------------------------
insert into @_PKsyslist (COLUMN_NAME, ORDINAL_POSITION)
select COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON tc.TABLE_NAME = kcu.TABLE_NAME AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME and tc.CONSTRAINT_TYPE = N'PRIMARY KEY'
  where kcu.TABLE_NAME = @_destination_Table and kcu.CONSTRAINT_SCHEMA = @_destination_schema ORDER BY kcu.ORDINAL_POSITION;              
 ---------------------------------------------------------------------------------------------------------
 -- assemble for MERGE statement:  t. = s. , - Separate to use ordinal position
 ---------------------------------------------------------------------------------------------------------
  Select @colorder = 0, @maxcol = 0, @_PKforMergeList = '', @_PKforCIDXList = '';
  Select @colorder = min(ORDINAL_POSITION), @maxcol = max(ORDINAL_POSITION)   from @_PKsyslist;          
  while @colorder <= @maxcol and @colorder > 0 
   begin
    select @colname = ''; 
    select @colname = cast(COLUMN_NAME as varchar(128)) from @_PKsyslist as l where l.ORDINAL_POSITION = @colorder;
    select @colname = '[' + @colname + ']';
    select @_PKforMergeList = @_PKforMergeList + 't.' + @colname + ' = s.' + @colname  ;
    select @_PKforCIDXList = @_PKforCIDXList + ' ' + @colname;
    if @colorder != @maxcol
     begin
     select @_PKforMergeList = @_PKforMergeList + ' and ';
     select @_PKforCIDXList = @_PKforCIDXList + ',';
     select @colorder = min(ORDINAL_POSITION) from @_PKsyslist as l where l.ORDINAL_POSITION > @colorder;
     end
    else 
     select @colorder = -9;
   end; 
   if  ltrim(rtrim(@_PKforMergeList)) = '' 
    select @_PKforMergeList = 'looks like your destination table does not have a declared PK';
    ----------------------------------------------------------------------------------------
    -- get max/min col info for table
    ----------------------------------------------------------------------------------------
    Select @colorder = 0, @maxcol = 0;
    Select @colorder = min(colorder), @maxcol = max(colorder)   from sys.syscolumns where  id = @_dest_object_id;     
    while  @colorder <= @maxcol
     begin

      select @colname = '', @updatecolname = '', @colxtype = 0;
      -----------------------------
      -- get column name, add brackets  [ ]  
      select @colname = '[' + [name] + ']', @colxtype = xtype from sys.syscolumns where  id = @_dest_object_id and colorder = @colorder;

      -- OPEN Query build; @openquery, WITHOUT TRAILING COMMA
      if @colxtype in ( 167, 231) 
        select @openquery =  @openquery + @colname ; 
      else
        select @openquery =  @openquery + @colname;  

      -- add to column name
      if @colorder != @maxcol
       begin                 
        select @updatecolname = 't.' + @colname + ' = s.' + @colname + ',';
        select @colname   = @colname + ',';
        select @openquery  = @openquery + ',';
       end
      else                  
        select @updatecolname = 't.' + @colname + ' = s.' + @colname + '  -- the end';
                       
      ----------------------------------------------
      -- build out the insert/values
       
      select @insert = @insert + '  ' + @colname;
      select @Values = @Values + 's.' + @colname;
      select @update = @update + @updatecolname ;
      -------------------------------
      -- get next column order
      select @colorder = min(colorder) from sys.syscolumns where  id = @_dest_object_id and colorder > @colorder;
      -- bump counter 
      set @counter = @counter + 1;
     end;      
      
    -------------------------------------------------------------------------------------------------------------------------
    -- MERGE build
    select @MergeSection = 
      '    MERGE [' + @_destination_schema + '].[' + @_destination_Table + '] as t'  + CHAR(13) + CHAR(10) 
     + '    USING [' + @_source_schema      + '].[' + @_source_TableView  + '] as s' + CHAR(13) + CHAR(10) 
     + '    on (' + @_PKforMergeList + ')     -- pk INFO HERE; FORMAT t. = s.)   '   + CHAR(13) + CHAR(10) 
     +  CHAR(13) + CHAR(10) 
     + '    WHEN MATCHED AND NOT EXISTS(SELECT s.* INTERSECT SELECT t.*) '+ CHAR(13) + CHAR(10) 
     + '    THEN UPDATE SET   -- format:  t. = s. , ....'+ CHAR(13) + CHAR(10);

    select @Merge1Section = '    ' + @update + CHAR(13) + CHAR(10)  ;
      
    select @Merge1aSection = 
      '    WHEN NOT MATCHED BY TARGET' + CHAR(13) + CHAR(10)  
     + '    THEN ' + CHAR(13) + CHAR(10) 
     + '     INSERT  (' + @insert + ')' +  CHAR(13) + CHAR(10);

    select @Merge2Section = 
      '     VALUES ( '  +  @values + ')' +  CHAR(13) + CHAR(10)
     + CHAR(13) + CHAR(10) 
     + '   WHEN NOT MATCHED BY SOURCE then ' + CHAR(13) + CHAR(10) 
     + '      DELETE;' + CHAR(13) + CHAR(10) 
     + CHAR(13) + CHAR(10);  

     select @SPSection_cTwo = 
       CHAR(13) + CHAR(10) 
      + ' /* ------------------------------------------------------------------------------------------------------'  +  CHAR(13) + CHAR(10) 
      + '    MERGE'  +  CHAR(13) + CHAR(10) 
      + '    WHEN MATCHED AND NOT EXISTS(SELECT Source.* INTERSECT SELECT Target.*) THEN'  +  CHAR(13) + CHAR(10) 
      + '     (requires 1:1 column match between source and target, target requires PK)'  +  CHAR(13) + CHAR(10) 
      + '    ------------------------------------------------------------------------------------------------------ */'  +  CHAR(13) + CHAR(10) 
      + '   '  +  CHAR(13) + CHAR(10) 
     -- +  CHAR(13) + CHAR(10);
     -- chunked to support debugging
     select @_procedure_all = @_procedure_all + @SPSection_cTwo;
     select @_procedure_all = @_procedure_all + @MergeSection;  
     select @_procedure_all = @_procedure_all + @Merge1Section;  
     select @_procedure_all = @_procedure_all + @Merge1aSection; 
     select @_procedure_all = @_procedure_all + @Merge2Section;  
             
 if len(@_procedure_all)  < 8000
 select @_procedure_all;
 else 
 begin
  select  @SPSection_cTwo; 
  select  @MergeSection  ; 
  select  @Merge1Section ; 
  select @Merge1aSection ; 
  select @Merge2Section  ; 
 end

       
 

Compare Two Tables and Columns - Meta-data T-SQL script

You need a tool to compare the meta- data between two tables, and you don't have the budget for one of the great tools already out in the market.  The script below will display all the differences between two tables for the main table and column meta-data attributes.

       
----------------------------------------------------------------------------
-- the two databases to be compared
-- user data entered here:
----------------------------------------------------------------------------
declare  @MyLegacyDatabase  nvarchar(128)  = N'[MyLegacyDatabase]' 
        ,@MyProposedDatabase nvarchar(128) = N'[MycompareDatabase]' 
        ,@Objecttype   nchar(1)    = N'U' ; 

----------------------------------------------------------------------------
-- variables and setup
----------------------------------------------------------------------------
declare @LegacyQuery nvarchar(max) = '', @ProposedQuery nvarchar(max) = '';
select  @LegacyQuery = 
  'select  Tablename = o.[name], Columnname =  c.[name], c.[column_id], cast(t.name as varchar(15)) as datatype, c.system_type_id, c.user_type_id, c.max_length, c.[precision], c.scale '
 +   ' from ' + @MyLegacyDatabase + '.sys.all_columns as c '
 +   ' inner join ' + @MyLegacyDatabase + '.sys.all_objects as o on c.object_id = o.object_id and o.type = '''  + @Objecttype + ''' and o.is_ms_shipped = 0 '
 +   ' inner join ' + @MyLegacyDatabase + '.sys.systypes as t  on c.system_type_id = t.xtype; ';
select  @ProposedQuery = 
  'select  Tablename = o.[name], Columnname =  c.[name], c.[column_id], cast(t.name as varchar(15)) as datatype, c.system_type_id, c.user_type_id, c.max_length, c.[precision], c.scale '
 +   ' from ' + @MyProposedDatabase + '.sys.all_columns as c '
 +   ' inner join ' + @MyProposedDatabase + '.sys.all_objects as o on c.object_id = o.object_id and o.type = '''  + @Objecttype + ''' and o.is_ms_shipped = 0 '
 +   ' inner join ' + @MyProposedDatabase + '.sys.systypes as t  on c.system_type_id = t.xtype; ';
select @LegacyQuery;  

declare @tblLegacycompare TABLE ( [Tablename] [sysname] NULL, [Columnname] [nvarchar](128) NULL, [column_id] int null, [datatype] [varchar](15) NULL, [system_type_id] [tinyint] NULL, [user_type_id] [int] NULL, [max_length] [smallint] NULL, [precision] [tinyint] NULL, [scale] [tinyint] NULL);
declare @tblProposedcompare TABLE ( [Tablename] [sysname] NULL, [Columnname] [nvarchar](128) NULL, [column_id] int null, [datatype] [varchar](15) NULL, [system_type_id] [tinyint] NULL, [user_type_id] [int] NULL, [max_length] [smallint] NULL, [precision] [tinyint] NULL, [scale] [tinyint] NULL);

--select top 1 * from @tblLegacycompare; 
--EXEC sp_executesql @LegacyQuery;
--select @LegacyQuery;   

----------------------------------------------------------------------------
-- populate the tables with data to compare
----------------------------------------------------------------------------
           
insert into @tblLegacycompare (Tablename, Columnname, column_id, datatype, system_type_id, user_type_id, max_length, [precision], scale) 
 EXEC sp_executesql @LegacyQuery;
 -- select * from @tblLegacycompare;

insert into @tblProposedcompare (Tablename, Columnname, column_id, datatype, system_type_id, user_type_id, max_length, [precision], scale) 
 EXEC sp_executesql @ProposedQuery;
 --  select * from @@tblProposedcompare;

----------------------------------------------------------------------------
-- compare
----------------------------------------------------------------------------

select TableSort = isnull(n.Tablename ,o.Tablename ), ColumnSort = isnull(n.Columnname, o.Columnname)
,OnLegacyDb  = case when  o.Tablename is not null then 'Yes' else 'No' end 
,OnProposedDb = case when  n.Tablename is not null then 'Yes' else 'No' end 
,ColumnAddDrop = case when o.Columnname = n.Columnname then '-'      when o.Columnname is null and n.Columnname is not null then 'ADD' when o.Columnname is not null and n.Columnname is null then 'DROP' else '???' end
,ColumnIDChg   = case when o.column_id  = n.column_id  then 'no chg' when o.column_id  is null and n.column_id  is not null then 'ADD' when o.column_id  is not null and n.column_id  is null then 'DROP' else 'CHANGE' end


,ColumnTypeChg = case when isnull(o.datatype,'') = 'varchar' and isnull(n.datatype,'') = 'char' and isnull(o.max_length,-1) = isnull(n.max_length,-2) then 'no chg(H)'
      when o.datatype is null then 'add' when n.datatype is null then 'drop' when  o.datatype != n.datatype then 'CHANGE' else 'no chg' end

,ColumnLengthChg = case when o.max_length  is null then 'add' when n.max_length  is null then 'drop' when o.max_length  != n.max_length  then 'CHANGE' else 'no chg' end
,ColumnPrecisionChg = case when o.[precision] is null then 'add' when n.[precision] is null then 'drop' when o.[precision] != n.[precision] then 'CHANGE' else 'no chg' end
,ColumnScaleChg  = case when o.scale    is null then 'add' when n.scale  is null then 'drop' when o.scale       != n.scale  then 'CHANGE' else 'no chg' end
-- ,o.Tablename as TblOld, n.Tablename as TblNew, o.Columnname as ColOld, n.Columnname as ColNew
, o.datatype as TypeOld, n.datatype as TypeNew, o.column_id as ColIDOld, n.column_id as ColIDNew
, o.max_length as LENold, n.max_length as LENnew, o.[precision] as PrecisionOld, n.[precision] as PrecisionNew, o.scale as ScaleOld, n.scale as ScaleNew
, o.system_type_id as SysTypeOld, o.user_type_id as UserTypeOld, n.system_type_id as SysTypeNew, n.user_type_id as UserTypeNew

from @tblLegacycompare as o full outer join @tblProposedcompare as n on o.Tablename = n.Tablename and o.Columnname = n.Columnname

order by isnull(n.Tablename ,o.Tablename ) asc , isnull(n.Columnname, o.Columnname) asc ;
       
 

IDENTITY Column - and the steps to remove from your table

Issue:

Your table has a column identified as an IDENTITY (autonumber) column, and you need to

1) keep the column and its contents
2) remove the IDENTITY attribute for the column.

Approach:

As of 2017, SQL Server does not have an ALTER TABLE command that allows you to remove the IDENTITY property. The basic steps to remove an IDENTITY property are as follows:

- add a new column, and copy contents into the new column
- drop any indexes and constraints using the TO BE dropped column
- drop the old column
- rename the new column with the old name
- rebuild indexes and constraints

Here is a sample set up scripts to get the job done:

The first part creates a test table, the second part removes the IDENTITY property.

-------------------------------------------------------
-- setup:

use work;
go

create table work.dbo.testidentity
(  tblid     int identity(1,1) not null
  ,somevalue varchar(20) null
  );

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);

  insert into  work.dbo.testidentity (somevalue)
  values ( 'one' )
,( 'two' )
,( 'three' )
,( 'four' );

select * from work.dbo.testidentity ;

-------------------------------------------------------
-- the process to remove:

alter table work.dbo.testidentity add temp_tblID int null;
go
select * from work.dbo.testidentity ;
go

update work.dbo.testidentity set temp_tblID = tblid;
go
alter table work.dbo.testidentity alter column temp_tblID int not null;
go
select * from work.dbo.testidentity ;
go
alter table work.dbo.testidentity drop  constraint pk_tblid;
go
alter table work.dbo.testidentity  drop column tblid ;
go
select * from work.dbo.testidentity ;
go
EXEC sp_rename 'dbo.testidentity.temp_tblID' , 'tblID', 'COLUMN';
go
select * from work.dbo.testidentity ;
go

alter table work.dbo.testidentity add constraint pk_tblid  primary key clustered ( tblID);
go

Fix Windows 10 - 100% Disk Usage in Task Manager

A colleague directed me to the URl below with a list of tips to fix the 100% disk usage problem I was having with Windows 10.   I also disabled all the Google Chrome Sync "on - custom settings"

You can find the site here:  Windows 10 100% disk usage in Task Manager.

I only needed to do a few steps:

1. disable windows search
2. disable SuperFetch service  (see registry adjustments below)
3. disable all Google Chrome:  Sync "on - custom settings"

A failing SAS hard drive on my RAID :

I've setup my primary workstation with RAID 1 using an LSI SAS 3000 series (1068E) controller.  When disk usage started to spike again, the problem was with the hard drive.  The RAID controller was struggling to maintain consistency with the failing SAS drive. Once the hard drive was replaced, and the RAID rebuilt, disk usage for the RAID partition was back to normal.

Other notes:

I eventually, changed the registry settings for memory management:

Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\PrefetchParameters\

• EnablePrefetcher:   change from 3 to 0
• EnableSuperfetch:  change from 3 to 0

Background Intelligent Transfer Service:  I have it set to 'Manual' and is typically not running