Database

ALTER PROCEDURE dbo.UpdateTableY
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    
	DECLARE @MYCURSOR CURSOR
	DECLARE @ROWID INT
	DECLARE @COL2_X INT
	DECLARE @COL3_X INT
	DECLARE @COL2_Y INT
	DECLARE @COL3_Y INT
    
	
	SET @MYCURSOR = CURSOR FOR

	SELECT A.ROWID,A.COL2,A.COL3
    FROM (SELECT 
				ROW_NUMBER() OVER (ORDER BY COL1) AS ROWID,
				COL2 , COL3 FROM tblX) A 

	OPEN @MYCURSOR
	FETCH NEXT
	FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X

	WHILE @@FETCH_STATUS = 0

	BEGIN

		-- GET THE COL2 VALUES BASED ON ROW ID'S FROM tblY
		SELECT @COL2_Y = COL2 
		FROM tblY
		WHERE COL1 = @ROWID

		--MATCH THE COL2 RECORDS OF BOTH TABLES
		IF(@COL2_Y = @COL2_X)

         BEGIN 
				-- GET THE COL3 VALUES BASED ON ROW ID'S FROM tblY
				SELECT @COL3_Y = COL3 
				FROM tblY
				WHERE COL1 = @ROWID

				--IF A MISMATCH BETWEEN THE COL3 RECORDS OF BOTH TABLES
				-- THEN UPDATE COL4 OF TABLE Y
				IF(@COL3_Y <> @COL3_X)

				BEGIN

					UPDATE tblY

					SET COL3 = @COL3_X,
						COL4 = 1

					WHERE COL1 = @ROWID
				 
				END

		 END	

	FETCH NEXT
	FROM @MYCURSOR INTO @ROWID,@COL2_X,@COL3_X

	END

	CLOSE @MYCURSOR
	DEALLOCATE @MYCURSOR
	
END
GO

SELECT 

		P.ROLENAME,
		P.ROLEDESC, 
		P.ROLEUSER AS 'PRIMARY',
		B.ROLEUSER AS 'BACKUP'

FROM    (	
        SELECT ROLENAME,ROLEDESC,ROLETYPE ,ROLEUSER 
	FROM TBLROLE
	WHERE ROLETYPE = 'PRIMARY') P 

INNER JOIN (
	SELECT ROLEUSER ,ROLEDESC
	FROM TBLROLE
	WHERE ROLETYPE = 'BACKUP')B

ON      P.ROLEDESC = B.ROLEDESC

SELECT *
  FROM sysobjects
 WHERE NAME LIKE ('%name%')

I are troll

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager needed in this project (mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%Project Manager %')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%(mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('%ProjectManager needed in this project (mandatory)%')

SELECT ROLENAME
FROM TBLROLE
WHERE RoleName LIKE ('% (mandatory) %')

1) a)How to list and view the system tables like sys.databases, sysobjects, ...etc in the SQL 2005 management studio?<br />

SELECT * FROM SYS.DATABASES

     SELECT * FROM SYS.OBJECTS

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

<code>CREATE VIEW sys.objects</code> AS   SELECT name, object_id, principal_id, schema_id, parent_object_id,    type, type_desc, create_date, modify_date,    is_ms_shipped, is_published, is_schema_published   FROM sys.objects$  

CREATE TRIGGER SOME_TRIGGER ON spt_fallback_dev

FOR UPDATE AS

SELECT * FROM spt_fallback_dev

CREATE TABLE SAMPLE_TABLE 
(
	EID INT,
	ENAME VARCHAR (12)
);

CREATE TRIGGER SAMPLE_TRIGGER_A ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

CREATE TRIGGER SAMPLE_TRIGGER_B ON SAMPLE_TABLE

FOR UPDATE AS

SELECT * FROM SAMPLE_TABLE

SELECT NAME 
FROM SYSOBJECTS
WHERE DELTRIG = (
SELECT ID 
FROM SYSOBJECTS 
WHERE NAME = 'SAMPLE_TABLE')

NAME
------------------
SAMPLE_TRIGGER_A
SAMPLE_TRIGGER_B

select p.rolename, p.roledesc, p.roleuser, b.roleuser
from RoleTable p, RoleTable b
where p.roletype = 'Primary'
and p.rolename = b.rolename
and b.roletype = 'Backup'

Category      Zero  Total    Sum
Devices          0  38111   38111
Plan         38111   6063   44147
Improvements 44174   2266    4640

DECLARE  myCURSOR CURSOR FOR 
	  SELECT CATEGORY, TOTAL
	  FROM myTABLE

OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

SET @PBAL = 0.0
	
WHILE(@@FETCH_STATUS=0)	BEGIN

  SET @GTOTAL = @PBAL + @XTOTAL

  SET @MYSQL = 'INSERT INTO #MYTEMP'
  SET @MYSQL = @MYSQL + ' VALUES(' + CHAR(39) + @XCATEGORY+ CHAR(39) + ',' + @PBAL, + ',' + @XTOTAL + ','
  SET @MYSQL = @MYSQL + @GTOTAL + ')'

  PRINT @MYSQL

  EXEC(@MYSQL)

  FETCH NEXT FROM myCURSOR INTO @XCATEGORY, @XTOTAL

END

CLOSE myCURSOR
DEALLOCATE myCURSOR

GO