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