SQLServer obtains all database names, table names, stored procedures, and parameter lists.

Source: Internet
Author: User
1. Get all usernames: SELECTnameFROMSysuserswherestatus2andislogin1islogin1 indicates the account islogin0 indicates the role status2 indicates the user account status0 indicates the unified account 2. Get all database names: SELECTNameFROMMaster...

1. get all user names: SELECTnameFROMSysusers where status = '2' andislogin = '1' islogin = '1' indicates account islogin = '0' indicates role status = '2' indicates User Account status = '0' indicates guest System Account 2. get all database names: SELECTNameFROMMaster .. sysDatab

1. Get all user names:
SELECT name FROM Sysusers where status = '2' and islogin = '1'
Islogin = '1' indicates the account
Islogin = '0' indicates the 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. Retrieve 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 '))


[Comprehensive network data sorting]

1. Get all database names:
(1) Select Name FROM Master .. SysDatabases order by Name
2.Get all table names:
(1) Select Name FROM SysObjects Where XType = 'U' orDER BY Name
XType = 'U': indicates all user tables;
XType = 's': indicates all system tables;

(2) SELECT name FROM sysobjects WHERE type = 'U' AND sysstat = '83'

Note: Generally, only type = 'U' is required, but sometimes the system tables are mixed in (I don't know why). After adding the following sentence, these system tables can be deleted.


3.Get all field names:
(1) Select Name FROM SysColumns Where id = Object_Id ('tablename ')

(2) SELECT syscolumns. name, policypes. name, syscolumns. isnullable, syscolumns. length FROM syscolumns, policypes WHERE syscolumns. xusertype = policypes. xusertype AND "syscolumns. id = object_id ('tablename ')

Note:
(A) In order to highlight some important content, several pieces of information are selected for output.
(B) The syscolumns table only contains data type numbers. To obtain the complete name, you need to find it from the categorypes table. Generally, it is better to use xusertype for user data types, there will be no one-to-many cases.
(C) syscolumns. length is the length of the physical memory, so nvarchar, varchar, and Other types are shown in the database as half of this.

4. Obtain the column names of the primary keys in the table:

SELECT syscolumns. name FROM syscolumns, sysobjects, sysindexes, sysindexkeys WHERE syscolumns. id = object_id ('tablename') AND sysobjects. xtype = 'pk' AND sysobjects. parent_obj = syscolumns. id AND sysindexes. id = syscolumns. id AND sysobjects. name = sysindexes. name AND sysindexkeys. id = syscolumns. id AND sysindexkeys. indid = sysindexes. indid AND syscolumns. colid = sysindexkeys. colid

Note: This is found in four system tables. The relationship is complex and can be roughly expressed:
Syscolumns contains the column information and table id in the table. The sysobjects table contains the primary key name (similar to PK_Table) and Table id. sysindexes contains the primary key name, table id, and index number, sysindexkeys contains the table id, index number, and column number. After one item is matched, the column name can be found.

Http://www.cnblogs.com/icebutterfly/archive/2012/08/07/2626845.html

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.