SQL statement-obtain the database name, table name, stored procedure, and parameter list

SQL statement-obtain the database name, table name, stored procedure, and parameter list

I have been familiar with data transfer services of different databases. To facilitate data import and export, I developed a small tool to export and import data, the SQL statements that involve parameters such as database name and table name are summarized as follows:

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 the Guest 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 ('tablename ')

5. Retrieve all database types

Select name from policypes

6. Obtain the primary key field

Select Name from syscolumns where ('table name') and colid = (select top 1 keyno from sysindexkeys where ('table name '))

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 ('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. The sysindexes table 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.