System Tables : Part 1

/* Security Context of the Database */

--sql logins for the Database
---------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntname = 0

-- windows logins for Database
--------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntname = 1

--windows groups for the Database
--------------------------------
select uid,status,name,convert(varchar(20),createdate)as Createddate ,convert(varchar(20),updatedate) as Lastupdated from sysusers where isntgroup = 1

--Finding the Sql users not having logins
------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where issqluser = 1

--Find the useraccounts having dbaccess
----------------------------------------------------
select uid,status,name,convert(varchar(20),createdate) as Createddate,convert(varchar(20),updatedate) as Lastupdated from sysusers where hasdbaccess = 1


--Stored Procedures
--Displaying the Name and Text of the Stored Procedures.
-----------------------------------------------------------------------------
select so.id,so.name,sys.text from sysobjects sojoin syscomments sys on so.id = sys.idwhere so.xtype ='p'and so.base_schema_ver = 0

--Displaying the Stored Procedures which are Encrypted.
----------------------------------------------------------------------------
select so.id,so.name from sysobjects sojoin syscomments sys on so.id = sys.idwhere so.xtype ='p'and so.base_schema_ver = 0and sys.encrypted = 1

--Displaying all the Columns for a particular Table in T-SQL
---------------------------------------------------------------------------

select sys.name from syscolumns sys join sysobjects soon sys.id = so.id where so.name ='Orders'

--Displaying the Columns in the Table having the Identity Columns
----------------------------------------------------------------------------------
select name from syscolumns where status = 0x08

General Sql Server Backup in my organisation

The Sql Server Backup Plan

a.Scheduling a FullDiskBackup of the Databases at 8:00 AM in the Morning (before the office starts)
b.Doing a Transactional log for every 3 hours for each database applications alternatively one hour
Eg : Database Application A - 9:00 AM ; 12:00AM ; 3:00 PM;6:30PM
Database Application B - 9:30 AM ; 12:30AM ; 3:30 PM ;6:30PM

As the Number of database applications increase we try to decrease the time slot between the 2 databasesThe entire Disk Backup is then Copied back to the Main Veritas Backup System(Regular Backup System)

We take the Daily Backup tapes with us and we store the weekly Backups in a Remote LocationWhen the Month Ending tapes are ready we store these tapes and get back the weekly backups for the Next weekly usage