SQL obtains all user names, database names, all table names, all field names, and field types.

SQL obtains all user names, database names, all table names, all field names, and field types.
 2009  -  03  -  14   10 : 48 
 1 . Get all user names:
 Select Name From Sysusers Where Status = '  2  '   And Islogin =  '  1  ' 
Islogin =  '  1  ' Account
Islogin =  '  0  ' Role
Status =  '  2  ' Indicates the user account
Status =  '  0  ' Indicates a unified account.
 2 . Get all database names:
 Select Name From Master.. sysdatabases Order   By Name
3 . Get all table names
 Select Name From Databasename .. sysobjects Where Xtype =  '  U  '   Order   By Name
Xtype =  '  U  ' : Indicates all user tables;
Xtype =  '  S  ' : Indicates all system tables;
 4 . Get all field names:
 Select Name From Syscolumns Where ID =  Object_id ( '  Tablename  ' )
5 . Get all database types
 Select Name From Policypes
 6 . Obtain the primary key field
 Select Name From Syscolumns Where ID =  Object_id ( '  Table Name  ' ) And Colid = ( Select   Top   1 Keyno From Sysindexkeys Where ID =  Object_id ( '  Table Name  ' ))

 7 Obtain the field type

 Select A. NameAs   [  Column  ] , B. Name As Type From Syscolumns A, policypes B Where A. ID =  Object_id ( '  Table Name  ' ) And A. xtype = B. xtype
Or you can use the Stored Procedure
Exec Sp_help table name

 8 , Table Structure

 Select Column_name, data_type, character_maximum_length From Information_schema.columns Where Table_name =   '  Table Name  ' 

 /* **************************************** **************************************** **************************************** **  */ 

 /*  -- Obtain information about connecting to the SQL Server

All connected to the local machine: Database Name of the operation, computer name, user name, Nic physical address, IP address,ProgramName

-- Producer build 2003.11 (reference please keep this information )--  */ 

 /*  -- Call example
-- Display the connection information of all hosts
Exec p_getlinkinfo

-- Display the connection information of all hosts, including IP addresses
Exec p_getlinkinfo @ includeip = 1

-- Display the information of the specified database to be connected.
Exec p_getlinkinfo 'customer information'
--  */ 
 Create  Proc P_getlinkinfo
 @ Dbname Sysname =  Null , --  The name of the database to be queried. The connection information of all databases is queried by default.  
  @ Brief deip   Bit  =  0   --  Whether to display IP addresses. This control is added because it takes a long time to Query IP addresses.  
  As 
Declare   @ Dbid   Int 
 Set   @ Dbid  =  Db_id ( @ Dbname )

 Create   Table # Tb (ID Int   Identity ( 1 , 1 ), Dbname sysname, hostnameNchar ( 128 ), Loginname Nchar ( 128 ), Net_address Nchar ( 12 ), Net_ip Nvarchar ( 15 ), Prog_name Nchar ( 128 ))
 Insert   Into # Tb (hostname, dbname, net_address, loginname, prog_name)
 Select   Distinct Hostname, Db_name (Dbid), net_address, loginame, program_name From Master .. sysprocesses
 Where Hostname <>  ''   And ( @ Dbid   Is   Null   Or Dbid=  @ Dbid )

 If   @ Brief deip  =  0   Goto Lb_show --  If the IP address is not displayed, the IP address is displayed directly.  
 
 Declare   @ SQL   Varchar ( 500 ),@ Hostname   Nchar ( 128 ), @ ID   Int 
 Create   Table # IP (hostname Nchar ( 128 ), Varchar ( 200 ))
 Declare TB Cursor LocalFor   Select   Distinct Hostname From # TB
 Open TB
 Fetch   Next   From TB Into   @ Hostname 
 While   @ Fetch_status  =  0 
Begin 
 Set   @ SQL  =  '  Ping  '  +  @ Hostname  +  '  -A-N 1-L 1  ' 
 Insert # IP () Exec Master .. xp_mongoshell @ SQL 
Update # IP Set Hostname =  @ Hostname   Where Hostname Is   Null 
 Fetch   Next   From TB Into   @ Hostname 
 End 

 Update # TBSet Net_ip = Left (, Patindex ( '  %: %  ' ,) -  1 )
 From # TB Inner   Join (
 Select Hostname, =  Substring (,Patindex ( '  Ping statistics for %: %  ' ,) +  20 , 20 ) From # IP
 Where A Like   '  Ping statistics for %: %  ' ) B On A. hostname= B. hostname

Lb_show:
 Select ID, dbname As   '  Database Name  ' , Hostname As   '  Client name  ' , Loginname As   '  User Name  '   
, Net_addressAs   '  Nic physical address  ' , Net_ip As   '  IP address  ' , Prog_name As   '  Application name  '     From # TB

 Go   

From: http://blog.csdn.net/painss/article/details/4324973