Home Database Mysql Tutorial sql语句查询数据库中的表名/列名/主键/自动增长值实例

sql语句查询数据库中的表名/列名/主键/自动增长值实例

Jun 07, 2016 pm 06:07 PM
primary key List Table Name

sql语句查询数据库中的表名/列名/主键/自动增长值实例,需要的朋友可以参考下

sql语句查询数据库中的表名/列名/主键/自动增长值

----查询数据库中用户创建的表

----jsj01 为数据库名

select name tablename from jsj01..sysobjects where type='U' and name not in ('dtproperties')

--查询表里的字段信息

---docs为表名
---- select * from syscolumns where id = object_id('docs')

----查询数据库中所有类型
----select name,xtype from systypes

----两表联查,显示表中所有字段和对应的数据类型

----syscolumns里字段‘xtype' 对应 systypes里的 ‘xusertype' ,systypes 里的‘name'字段就是字段的数据类型
----docs 为表名
select a.name as fieldname,b.name as type from
syscolumns as a
join systypes as b
on a.xtype = b.xusertype
where id=object_id('docs')

----docs为数据表名 : 查询表字段、类型、说明

select a.name fieldname,b.name type,c.value comment from
syscolumns as a
full join systypes as b
on a.xtype = b.xusertype
full join ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', 'docs', 'column', default) as c ----这是2000版本,2005把user改为schema
on a.name=c.objname COLLATE Chinese_PRC_CI_AS -----排序规则(有时不加也可以,如果两表的排序规则不同,则会报错)
--join sysproperties c
--on a.id=c.major_id
where id=object_id('docs')

----查询表里的主键,没有主键为空,如果是多个组合主键就有多个值 pk为主键 fk为外键
--- jsj01 为数据库名 docs为表名 fk表示外键

select column_name as primarykey,* from
[jsj01].INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where Table_name='docs' and constraint_name like 'fk_%'

--select * from sysobjects WHERE OBJECT_NAME(sysobjects.parent_obj)='docs' --and xtype='pk'
--select * from sysconstraints where id = object_id('docs')
--select * from syscolumns where id = object_id('docs')
--select * from sysindexes
--select * from sysindexkeys

----查询表中自动增长的字段,没有为空,如果有就只有一个
----docs为表名

SELECT a.name column_name,b.name data_type
FROM syscolumns a,systypes b
WHERE a.id=object_id('docs') and a.xtype = b.xusertype
AND a.autoval is not null


作者 pukuimin1226
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is mysql logical primary key What is mysql logical primary key Jul 10, 2023 pm 01:46 PM

MySQL logical primary key refers to the field or field combination used to identify a record in the database, but it is not unique. Logical primary keys are usually used for data queries and data operations. The logical primary key can be any field with identifying properties. For example, in the user table, the user name can be used as the logical primary key because it can be used to identify a record, but it is not unique.

How to set the return key and home key on Redmi K70Pro? How to set the return key and home key on Redmi K70Pro? Feb 23, 2024 pm 01:40 PM

Mobile phones are now a necessity for young and middle-aged people. Of course, people of each age group have different needs for mobile phones. As one of the more popular models now, RedmiK70Pro has a very diverse range of functions and services that can meet the needs of consumers of different ages. How to set the return key and home key on Redmi K70Pro? You also need to understand it clearly. Only after you understand it can you decide whether to buy this mobile phone. Then follow the editor to take a look at the following content! How to set the return key and home key on Redmi K70Pro? To access your phone's settings menu, you can open the settings interface by pulling down the notification shade or looking for the settings icon on your home screen. In the settings interface, find and click "Button" or "Navigation Bar"

How to automatically associate MySQL foreign keys and primary keys? How to automatically associate MySQL foreign keys and primary keys? Mar 15, 2024 pm 12:54 PM

How to automatically associate MySQL foreign keys and primary keys? In the MySQL database, foreign keys and primary keys are very important concepts. They can help us establish relationships between different tables and ensure the integrity and consistency of the data. In actual application processes, it is often necessary to automatically associate foreign keys to the corresponding primary keys to avoid data inconsistencies. The following will introduce how to implement this function through specific code examples. First, we need to create two tables, one as the master table and the other as the slave table. Create in main table

What type is used for mysql primary key? What type is used for mysql primary key? Jul 18, 2023 pm 03:11 PM

MySQL primary key can use integer type, self-increasing integer type, UUID type or string type. Detailed introduction of types: 1. Integer type, one of the most common primary key types. In MySQL, different lengths can be used; 2. Self-increasing integer type, which can automatically assign a unique integer value as a primary key. This type is very suitable for use as Primary key, especially in scenarios that require high concurrent data insertion; 3. UUID type, a 128-bit globally unique identifier, which can ensure the global uniqueness of data; 4. String type, which is not recommended, etc.

How to set the return key and home key on Honor X50Pro? How to set the return key and home key on Honor X50Pro? Mar 18, 2024 am 11:34 AM

The honor Set return key and primary key? Let’s take a look below! How to set the return key and home key on Honor X50Pro? 1. Find the "Settings" icon on the home screen and click the "System and Updates" function option; 2. Click "System Navigation Method" in the System and Updates function item; 3. Honor mobile phones provide three return key settings, which can be set according to Adjust your own settings: The method of setting the return key and home key on the Honor X50Pro phone is very simple. You can follow the steps above.

How to modify column names in pandas How to modify column names in pandas Dec 01, 2023 pm 04:31 PM

Methods for modifying column names in pandas include using the rename() function to modify column names based on the dictionary, directly modifying the columns attribute and directly assigning values ​​to modify column names, and using the set_axis() function to modify all column names at once. Detailed introduction: 1. Use the rename() function, which accepts a dictionary as a parameter. The key of the dictionary is the original column name and the value is the new column name. 2. Directly modify the columns attribute. You can directly modify the columns attribute of the DataFrame object. Modify column names, etc.

How to set the return key and home key on Honor 90GT? How to set the return key and home key on Honor 90GT? Feb 12, 2024 am 09:48 AM

With the rapid development of the times, current mobile phone technology is also constantly improving. In the past, mobile phones had various buttons, but now they are gradually being eliminated. However, many people are still used to using buttons to control their mobile phones. So how to set the return button on Honor 90GT? and primary key? Let’s take a look below! How to set the return key and home key on Honor 90GT? Honor 90GT is a Huawei mobile phone. The method to set the return key and home key is as follows: Step 1: Open settings: Find the "Settings" icon on the phone desktop and click to enter the settings interface. Step 2: Find the "System and Updates" option: In the settings interface, slide down the screen, find and click to enter the "System and Updates" option. Step 3: Enter the navigation bar settings: In the system and update interface, slide down the screen and find

How to open the return key and home key on Redmi K70? How to open the return key and home key on Redmi K70? Feb 23, 2024 am 11:40 AM

RedmiK70 is a model used by many users. This phone has maintained a very good reputation since its release. It is very cost-effective and uses very powerful performance configurations. It also has many user-friendly functions that can make everyone You can choose by yourself, such as setting the return key and primary key, but how to set them specifically? How to set the return key and primary key on Redmi K70? Open the Settings app on your phone. Scroll down on the settings page, find and click "Additional Settings". After entering the additional settings page, select "Buttons and Gesture Shortcuts." In the Buttons and Gesture Shortcuts settings, you'll see tweaking options for the Back and Home keys. Click on the "Back Key" option and you can choose to enable or disable the hardware back key,

See all articles