The DaxMindMapper Reloaded

CREATE Procedure [dbo].usp_CreateDataLoadScript @TblName varchar(128) as
/*
exec sp_CreateDataLoadScript 'MyTable'

History
=======

22/08/2006 DR Amended to fix uniqueidentifier bug (there will be many others)

*/

create table #a (id int identity (1,1), ColType int, ColName varchar(128))

insert #a (ColType, ColName)
select case
when DATA_TYPE like '%char%' then 1
when DATA_TYPE like '%unique%' then 2
else 0 end ,
COLUMN_NAME
from information_schema.columns
where TABLE_NAME = 'CallResponseStatus' --@TblName
order by ORDINAL_POSITION

if not exists (select * from #a)
begin
raiserror('No columns found for table %s', 16,-1, @TblName)
return
end

declare @id int ,
@maxid int ,
@cmd1 varchar(7000) ,
@cmd2 varchar(7000)

select @id = 0 ,
@maxid = max(id)
from #a

select @cmd1 = 'select '' insert ' + @TblName + ' ( '
select @cmd2 = ' + '' VALUES ('' + '
while @id < @maxid
begin
select @id = min(id) from #a where id > @id

select @cmd1 = @cmd1 + '[' + ColName + '],'
from #a
where id = @id

select @cmd2 = @cmd2
+ ' case when ' + ColName + ' is null '
+ ' then ''null'' '
+ ' else '
+ case
when ColType = 1 then ''''''''' + ' + ColName + ' + '''''''''
when ColType = 2 then ''''''''' + ' + 'convert(varchar(100),' + ColName + ')' + ' + '''''''''
else 'convert(varchar(100),' + ColName + ')'
end
+ ' end + '','' + '
from #a
where id = @id
end

select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
select @cmd2 = left(@cmd2,len(@cmd2)-8) + '+ '')'' from ' + @tblName

select '/*' + @cmd1 + @cmd2 + '*/'

Create PROC dbo.usp_Repair_Orphan_Users(@targetdbname nvarchar(50) = null)
AS
-------------------------------------------------------------------------------
-- Description: This is a DBA utility used to resync all user/login sids,
--
-- Revision History
-- Date Author Revision Description
-- 09/07/2005 Tduffy Original version
-- 19/12/2005 DRussell Amended for so that database name must
-- be supplied.
-------------------------------------------------------------------------------
-- Parameters None
-------------------------------------------------------------------------------
-- Example
-- EXEC usp_Repair_Orphan_Users 'MyDBName'
-------------------------------------------------------------------------------

set nocount on

DECLARE @cmd varchar(4000)

BEGIN

Create table #Orphan_User_Tbl

(
[Database_Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned_User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry nvarchar(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL

BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND DATABASEPROPERTY(name, 'IsInload') = 0
AND DATABASEPROPERTY(name, 'IsInRecovery') = 0
AND DATABASEPROPERTY(name, 'IsInStandBy') = 0
AND DATABASEPROPERTY(name, 'IsReadOnly') = 0
AND DATABASEPROPERTY(name, 'IsNotRecovered') = 0
AND (@targetdbname is null OR Name = @targetdbname) AND name > @DBName -- DR Amend

)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
inner join master..sysxlogins b
on su.name=b.name
where
su.sid is not null
and su.sid not in (0x00,0x01)
and su.sid <> b.sid'

INSERT INTO #Orphan_User_Tbl EXEC (@Qry)
END

DECLARE MC CURSOR
READ_ONLY
FOR

SELECT [Database_Name]+ '..sp_change_users_login ''Update_One'' , ''' + Orphaned_User + ''',''' + Orphaned_User + ''''
FROM #Orphan_User_Tbl
ORDER BY [Database_Name], [Orphaned_User]

OPEN MC

FETCH NEXT FROM MC INTO @cmd
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
--Print @cmd
Execute (@cmd)
END
FETCH NEXT FROM MC INTO @cmd
END

CLOSE MC
DEALLOCATE MC

DROP Table #Orphan_User_Tbl

END