Database

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

DECLARE @LASTNAME VARCHAR(50)

FETCH VPres_Cursor INTO @LASTNAME

update dbo.VicePresident
        set dbo.VicePresident.LastName = @LASTNAME 

        FETCH VPres_Cursor INTO @LASTNAME

Declare csrPV Cursor For
	SELECT ColName_0 
	FROM stg.IRsCap
	WHERE ID > @HeadRow
OPEN csrPV 
FETCH NEXT FROM csrPV INTO @Value

WHILE @@FETCH_STATUS = 0
BEGIN	
	IF @Value != ''
		SET @LastValue = @Value
	
	IF @Value = ''
	BEGIN
		UPDATE STG.IRsCap SET ColName_0 = @LastValue
		WHERE CURRENT OF csrPV
	END	
	FETCH NEXT FROM csrPV INTO @Value
END
CLOSE csrPV
DEALLOCATE csrPV

SELECT ColName_0 	
FROM stg.IRsCap

WHERE ID > @HeadRow

IF @Value != ''		
SET @LastValue = @Value