USE [batchadmin]
GO


/****** Object:  StoredProcedure [batch].[spAddNewContext]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [batch].[spAddNewContext]( @name VARCHAR(32), @target VARCHAR(32), @usr VARCHAR(32), @passwd VARCHAR(32), @host VARCHAR(32), @cat VARCHAR(32), @privs VARCHAR(32), @rank smallint=1 )
AS 
    BEGIN
      
      IF EXISTS (SELECT name FROM Servers WHERE name=@name) 
        SELECT 'Context ' + @name + ' already exists!'
      ELSE
        BEGIN
          INSERT batch.servers
            VALUES(@name,1,@target,@usr,@host,@cat,0,@privs,@rank,'pooling = false;',@passwd)
          INSERT batch.servers
            VALUES(@name+' long',500,@target,@usr,@host,@cat,2,@privs,@rank,'pooling = false;',@passwd)
          SELECT 'Context ' + @name + ' created, server entries ''' + @name + ''' and ''' + @name + ' long'' added:'
          SELECT * FROM Servers WHERE name like '' + @name + '''%''' 
        END
    
    END
GO
/****** Object:  StoredProcedure [dbo].[spAddSchemaBrowser]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spAddSchemaBrowser]( @CONTEXT VARCHAR(32), @TYPE VARCHAR(16) = '' )  
AS
BEGIN	
SET NOCOUNT ON;

Declare @serverdbname varchar(100)
,@cmd nvarchar(max)
,@s varchar(max)  
,@tname varchar(40)
,@i int
,@mydbhost varchar(32)
,@mydbname varchar(32)
,@tableOwner varchar(32)
,@server varchar(32)


Delete from batch.Notes
where CONTEXT=@CONTEXT
and time = '1/1/2001'

IF (@TYPE = '')
	BEGIN
		SET @s = ' ' 
		Set @serverdbname = (select distinct HOST + '.' + cat  from batch.servers where Target = @context and ExecLimit=0)
		SET @tableOwner = 'dbo'
	END
ELSE
	BEGIN
		SET @s = ' '
		SET @mydbhost = (SELECT MyDBHost FROM batch.Users WHERE UserId = @CONTEXT)
		SET @mydbname = (SELECT MyDBName FROM batch.Users WHERE UserId = @CONTEXT)
		SET @tableOwner = (select usr FROM MyDBHosts WHERE MyDBHost = @mydbhost)
		SET @server = (select Server FROM MyDBHosts WHERE MyDBHost = @mydbhost)
		IF (CHARINDEX('.',@server) = 0)
			SET @serverdbname = @server + '.' + @mydbname
		ELSE
			SET @serverdbname = SUBSTRING(@server,1,CHARINDEX('.',@server)-1) + '.' + @mydbname
	END	

CREATE TABLE #dbcolumns(
	tablename varchar(128) NULL,
	name varchar(64) NULL,
	unit varchar(64) NULL,
	ucd varchar(128) NULL,
	enum varchar(64) NULL,
	description varchar(2000) NULL,
	rank int NULL default 0)
	
CREATE TABLE #dbObjects(
name varchar(128) NULL,
type varchar(64) NULL,
access varchar(8) NULL,
description varchar(256) NULL,
text varchar(7200) NULL,
rank int NULL default 0)

Set @cmd=''
Set @cmd = N'Insert into #dbObjects (name,type,access,description,text)' 
+ ' ' +'select name,type,access,description,text from'
+ ' ' +cast(@serverdbname as varchar(100)) + '.' + @tableOwner + '.dbObjects'
+ ' ' +'where type = ''U'''
exec sp_executesql @cmd
SELECT CAST(@@ROWCOUNT AS VARCHAR(6)) + ' lines inserted into #dbObjects.'

Set @cmd=''
Set @cmd = N'Insert into #dbcolumns (tablename,name,unit,ucd,enum,description)' 
+ ' ' +'select tablename,name,unit,ucd,enum,description from'
+ ' ' +cast(@serverdbname as varchar(100)) + '.' + @tableOwner + '.dbcolumns'
exec sp_executesql @cmd
SELECT CAST(@@ROWCOUNT AS VARCHAR(6)) + ' lines inserted into #dbColumns.'

Set @cmd=''
Set @cmd = N'Update C'
+' set rank = syscols.ordinal_position'
+' from #dbcolumns C'
+' join ' + cast (@serverdbname as varchar(100))+ '.' + 'INFORMATION_SCHEMA.COLUMNS syscols'
+' on C.tablename = syscols.table_name'
+' and C.name = syscols.column_name'
--select @cmd
exec sp_executesql @cmd
SELECT 'Set #dbColumns.rank.'


Create table #dbtname (count int NOT NULL IDENTITY (1, 1), tname varchar(40))

Insert into #dbtname(tname)
	select distinct name from #dbObjects

Create table #dbcoldesc (
	ColumnDescription nvarchar(max),
	tname varchar(40)
)

Set @i = 1

While (@i <= (select count(*) from #dbtname))
begin
	Set @tname = (select tname from #dbtname where count = @i)
	Set @s=' '
	select @s = COALESCE(@s + '<tr><td>',name) + name +'</td><td>'+ unit + '</td><td>' + ucd + '</td><td>' + enum +'</td><td>'+ description + '</td></tr>'  
	from #dbcolumns  
	where tablename = @TNAME  
	order by tablename, rank asc

	Insert Into #dbcoldesc
	select @s,@tname

	Set @i = @i + 1
end



Insert batch.Notes
	select '</pre><h4>'+a.tname+'</h4><div style="font-weight:bold">'+b.description+'</div><p style="width:800">'+b.text+'</p><table><tr><th>name</th><th>unit</th><th>ucd</th><th>enum</th><th>description</th></tr>'+c.ColumnDescription +'</table><pre>'
		,'1/1/2001',10000001,b.name,0,@context
	from #dbtname a
		join #dbObjects b on a.tname = b.name
		join #dbcoldesc c on b.name = c.tname	
		
Select 'Added Schema Browser for Context :: '+CAST(@context as varchar(40))


END


GO
/****** Object:  StoredProcedure [dbo].[spAddUserToGroup]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spAddUserToGroup]( @gName VARCHAR(32), @uName VARCHAR(32) )
AS 
    BEGIN
        DECLARE @gID BIGINT, @wsID BIGINT;

        SELECT @wsID=webservicesID 
        FROM batch.Users
        WHERE
            userid = @uName

        SELECT @gID = gid 
        FROM Groups
        WHERE
            [name] = @gName

        IF NOT EXISTS
            (SELECT gid, webservicesID FROM GroupMembers WHERE gid=@gID AND webservicesID=@wsID)
                INSERT GroupMembers VALUES( @gID, @wsID, 2 ) 
     END

GO
/****** Object:  StoredProcedure [dbo].[spChangeMyDBOwners]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Procedure  to set 'batch' to be owner of all MyDBs

CREATE PROCEDURE [dbo].[spChangeMyDBOwners]( @userName VARCHAR(64) )
AS BEGIN

	DECLARE @owner varchar(64), @dbname varchar(64), @cmd NVARCHAR(100), @FETCH_STATUS int;

	SELECT s.name INTO #mydbs FROM master.dbo.sysdatabases s 
	WHERE s.name LIKE 'mydb_%' 

	DECLARE DBcursor CURSOR FOR SELECT [name] from #mydbs
	SET @FETCH_STATUS = 0;

	OPEN DBcursor
	WHILE (1 = 1)
		BEGIN
			FETCH NEXT FROM DBcursor INTO @dbname
			SELECT @owner=u.name 
			FROM master.dbo.sysusers u
				JOIN master.dbo.sysdatabases d ON d.sid=u.sid
			WHERE d.name=@dbname
			IF (@owner != N'batch')
            -- if the owner isn't already 'batch'
				BEGIN
					SET @FETCH_STATUS = @@FETCH_STATUS;
					IF (@FETCH_STATUS != 0) BREAK;
					PRINT @dbname
					-- first transfer db_owner schema ownership to dbo
					SET @cmd = 'USE '+@dbname+'; ALTER AUTHORIZATION ON SCHEMA::db_owner TO [dbo]'
					EXEC sp_executesql @cmd
					-- next drop non-owner 'batch' user from db
					SET @cmd = 'USE '+@dbname+'; exec sp_dropuser N'''+@userName+''''
					exec sp_executesql @cmd
					-- then change current owner to be 'batch'
					SET @cmd = 'USE '+@dbname+'; exec sp_changedbowner N'''+@userName+''''
					exec sp_executesql @cmd
				END
		END
	CLOSE DBcursor;
	DEALLOCATE DBcursor;

	DROP TABLE #mydbs
END


GO
/****** Object:  StoredProcedure [dbo].[spExecuteSQL2]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Stored Procedure dbo.spExecuteSQL2    Script Date: 1/17/2006 4:44:20 PM ******/
--
CREATE PROCEDURE [dbo].[spExecuteSQL2](
	@cmd varchar(8000),
	@webserver varchar(64) = '',   -- the url
	@winname varchar(64)   = '',   -- the windows name of the server
	@clientIP varchar(16)  = '0',   -- client IP address
	@access varchar(64)    = ''    -- subsite of site,  if 'collab' statement 'hidden'
)
-------------------------------------------------------------------------------
--/H Procedure to safely execute an SQL select statement
-------------------------------------------------------------------------------
--/T The procedure runs and logs a query, but does not parse
--/T it. <br>
--/T See also spExecuteSQL
-------------------------------------------------------------------------------
AS
BEGIN
	SET NOCOUNT ON
	--
	DECLARE @error int,		 -- error number
		@errorMsg varchar(100),	 -- error msg
		@serverName varchar(32), -- name of this databaes server
		@dbName varchar(32),	 -- name of this database
		@isVisible int,		 -- flag says sql is visible to internet queries
		@startTime datetime,
		@endTime datetime,
		@busyTime bigint,
		@rows bigint,
		@IOs bigint
	--
	SET @isVisible = 1;
	SET @serverName = @@servername;
	SELECT @dbname = [name] FROM master.dbo.sysdatabases WHERE dbid = db_id();
	------------------------------------------------------------	
	IF (@errorMsg is null)		-- if good,
            BEGIN
		---------------------------------------------
            	--- log the command if there is a weblog DB
		-- variables to track and log SQL performance.
		----------------------------------------------
		if (0 != (select count(*) from master.dbo.sysdatabases where name = 'weblog'))
                  begin
			set @startTime = getUtcDate();
			set @busyTime = @@CPU_BUSY+@@IO_BUSY
			set @IOs = @@TOTAL_READ+@@TOTAL_WRITE
			insert WebLog.dbo.SqlStatementLogUTC 
				Values(@startTime,@webserver,@winName, @clientIP,
				@serverName, @dbName, @access, @cmd, @isVisible)
		  end
		------------------------
		-- execute the command
		------------------------
		exec (@cmd)
		set @rows = @@rowCount
		-----------------------------------------------------------
		-- record the performance when (if) the command completes.
		-----------------------------------------------------------
		if (@startTime is not null)
                    begin
			set @endTime = getUtcDate();
			insert WebLog.dbo.SqlPerformanceLogUTC
				values (@startTime,@webserver,@winName, @clientIP,
				datediff(ms, @startTime, @endTime)/1000.0,      -- elapsed time
				((@@CPU_BUSY+@@IO_BUSY)-@busyTime)/1000.0,      -- busy time
				@rows, @@procid, 0,'')				-- rows returned
		    end
	    END            -- end of good command case
	-------------------------------------------------------------------------
	-- bad input command case
	ELSE                    -- if error
	    BEGIN
		IF (0 != (select count(*) from master.dbo.sysdatabases where name = 'weblog'))
		    begin
			set @startTime = getUtcDate();
			insert WebLog.dbo.SqlStatementLogUTC
				values(@startTime,@webserver,@winName, @clientIP,
				@serverName, @dbName, @access, @cmd, @isVisible)
			insert WebLog.dbo.SqlPerformanceLogUTC
				values(@startTime,@webserver,@winName, @clientIP,
				0,0,0,@@procid, -1, @errorMsg)
		    end
               SELECT @errorMsg + @cmd as error_message; -- return the error message
	    END
	-------------------------------------------------------------------
END

GO
/****** Object:  StoredProcedure [dbo].[spFixMyDBOrphanedUser]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Procedure  to set 'batch' to be owner of all MyDBs

CREATE PROCEDURE [dbo].[spFixMyDBOrphanedUser]( @userName VARCHAR(64) )
AS BEGIN

	DECLARE @dbname varchar(64), @cmd NVARCHAR(100), @FETCH_STATUS int;

	SELECT s.name INTO #mydbs FROM master.dbo.sysdatabases s 
	WHERE s.name LIKE 'mydb_%' 

	DECLARE DBcursor CURSOR FOR SELECT [name] from #mydbs
	SET @FETCH_STATUS = 0;

	OPEN DBcursor
	WHILE (1 = 1)
	    BEGIN
			FETCH NEXT FROM DBcursor INTO @dbname
			SET @FETCH_STATUS = @@FETCH_STATUS;
	 		IF (@@fetch_status < 0) break
			PRINT @dbname
			SET @cmd = 'USE '+@dbname+'; EXEC sp_change_users_login N''UPDATE_ONE'', N'''+@userName+''', N'''+@userName+''''
			EXEC sp_executesql @cmd
	 
	    END
	CLOSE DBcursor;
	DEALLOCATE DBcursor;

	DROP TABLE #mydbs
END


GO
/****** Object:  StoredProcedure [dbo].[spTruncateMYDBLog]    Script Date: 9/19/2014 10:15:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[spTruncateMYDBLog] (@database_name varchar(64)=null)
AS
BEGIN	
SET NOCOUNT ON;

declare @i int = 1, @max int;
--declare @database_name varchar(64)
declare @maxsize bigint, @log_name varchar(64)
declare @sqlcmd nvarchar(1024)

SELECT rowid=IDENTITY (int, 1, 1)
,DB_NAME(l.[database_id]) AS database_name
,l.[name] AS logical_name
,l.[physical_name]
,l.[size]*8 [Size_KB]
,m.max_size*8 [MaxData_sizeKB]
into #BigLogList
FROM [master].[sys].[master_files] l
inner join
[master].[sys].[master_files] m
on l.database_id = m.database_id
and l.type = 1
and m.type = 0
where DB_NAME(l.[database_id]) like ISNULL(@database_name,'MYDB_%')
--and l.size*8 > m.max_size*8
and l.database_id not in (select s.dbid from sys.sysprocesses s)
order by l.size desc

Set @max = @@ROWCOUNT

While @i<=@max
Begin
select @sqlcmd = '';
Select @database_name = database_name, @maxsize= MaxData_sizeKB, @log_name= logical_name from #BigLogList where rowid = @i

set @sqlcmd= 'USE ['+@Database_name+'];' + CHAR(13) + CHAR(10) 
set @sqlcmd= @sqlcmd +'Alter database '+@Database_name+' SET Recovery simple;' + CHAR(13) + CHAR(10)
set @sqlcmd= @sqlcmd +'DBCC Shrinkfile('+@log_name+',3)' + CHAR(13) + CHAR(10)
set @sqlcmd= @sqlcmd +'Alter database '+@Database_name+' SET Recovery Bulk_Logged' + CHAR(13) + CHAR(10)
set @sqlcmd= @sqlcmd +'Alter database '+@Database_name+' MODIFY FILE (NAME = '+@log_name+', MaxSize='+cast(@maxsize as varchar(16))+'KB);' + CHAR(13) + CHAR(10)

exec sp_executesql @sqlcmd

Set @i = @i + 1;
End

drop table #BigLogList
END


GO


