Query the database name, data table name, and field name in SQL Server.

Source: Internet
Author: User

Database query name:

Select
Name from Master. DBO. sysdatabases
Where status
<> 512

Query table (tables) Name:

Select
Name from DBO. sysobjects
Where objectproperty (ID, n'isusertable ')
= 1 and
Name <> 'dtproperties'

Query the name of a data table with Schema (tables:
Select B. Name
+ '.'
+ A. Name
Name from sysobjects
Inner join
SYS. schemas B on A. uid = B. schema_id
Where objectproperty (ID, n'isusertable ')
= 1 and A. Name
<> 'Dtproperties'

Note: by default, the schema is DBO, and the data table name should be DBO. tablename. However, some data tables specify other schemas. For example, HumanResources. employee; production. Product in adventureworks.
Query the name of a field in a data table (tables:
Select
* From DBO. syscolumns
Where id = object_id (n' [production]. [product] ')
Order by colid

Some databases are case sensitive, such as adventureworks. note that the table name and schemas name must be correctly written. I just added DBO in one of my programs. sysobjects is written as DBO. sysobjects (uppercase) error.
PS: For more information about how to list the names of available database servers in a LAN, see my other log:

2010/05/28 added:
Debug on the company's network today. connect to an SQL Server 2000. an error occurred while loading the table name. later I compared SQL Server 2005 on my computer. no sys. schemas (view ). (It seems that the concept of schema does not exist. sorry, I am still a little white on the database .) after several attempts, because the sysobjects of SQL Server 2005 does not save files such as sys. views such as schemas. after multiple attempts, the compatibility problem is ugly:
Begin
If exists (select uid from sysobjects where name = 'sysobjects ')
Select B. name + '. '+. name from sysobjects a inner join sysusers B on. uid = B. UID where objectproperty (. ID, N 'isusertable') = 1 and. name <> 'dtproperties'
Else
Select B. name + '. '+. name from sysobjects a inner join sys. schemas B on. uid = B. schema_id where objectproperty (. ID, N 'isusertable') = 1 and. name <> 'dtproperties'
End

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.