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