SQL Server through the system information table for the table method of field information

2010-05-06  来源:本站原创  分类:Database  人气:152 

The fields of information access in the table:
select * from syscolumns where 'table name')

Primary key for the table method:
select * from sysobjects where xtype = 'pk'
Similarly the foreign key

For example: Query Table 'cdt_1x_basic' field information (including primary key information)
select a. *,
case b.IsPK when 1 then 1 else 0 end as IsPK
from
(
select * from syscolumns where'dbo. cdt_1x_basic ')
) A left join
(
SELECT TABLE_NAME, COLUMN_NAME, 1 as IsPK FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'cdt_1x_basic' - there must not add the prefix dbo.
) B on a.name = b.COLUMN_NAME

Annex 1, the system table syscolumns definition of the various fields and information:
Column Name Data Type Description
name sysname column name or the name of the process parameters.
id int table the column belongs to the object ID, or the parameters associated with the stored procedure ID.
xtype tinyint systypes the physical storage type.
typestat tinyint internal use only.
xusertype smallint extended user-defined data type ID.
The maximum length smallint systypes physical storage length.
xprec tinyint internal use only.
xscale tinyint internal use only.
colid smallint column or parameter ID.
xoffset smallint internal use only.
bitpos tinyint internal use only.
reserved tinyint internal use only.
colstat smallint internal use only.
cdefault int the column's default value ID.
The domain int column CHECK constraint rules or ID.
number smallint process packets (0 non-process items) sub-process number.
colorder smallint internal use only.
autoval varbinary (255) internal use only.
offset smallint the column where the row offset; if it is negative, said variable-length rows.
status tinyint columns or parameters used to describe the properties of the bitmap:

0x08 = Column allows null values.
0x10 = When adding varchar or varbinary column, ANSI padding effect. Varchar columns to retain trailing spaces, trailing zero retention varbinary column.
0x40 = parameter for the OUTPUT parameter.
0x80 = as an identity column.

type tinyint systypes the physical storage type.
usertype smallint systypes the user-defined data type ID.
printfmt varchar (255) internal use only.
prec smallint accuracy level of the column.
scale int the column number of decimal places.
iscomputed int calculate the column that has a sign:

0 = not calculated.
1 = has been calculated.

isoutparam int indicates that the process parameters are output parameters are:

1 = true.
0 = false.

isnullable int indicates that the columns allow null values:

1 = true.
0 = false.

Annex 2, table syscolumns data type in the corresponding note xtype
xtype type
34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 sysname
231 nvarchar
239 nchar

相关文章
  • Win7 64-bit SQL Server 2000 operating system installation issues encountered in [solution] 2011-01-08

    Win7 64-bit SQL Server 2000 operating system installation issues encountered] [intelligent software solutions workshop CLSW Chen Liang (buttoning a shirt: 40375363) http://clsw.blogcn.com/diary, 35403512.shtml 1,64-bit operating systems are not compa

  • SQL Server through the system information table for the table method of field information 2010-05-06

    The fields of information access in the table: select * from syscolumns where 'table name') Primary key for the table method: select * from sysobjects where xtype = 'pk' Similarly the foreign key For example: Query Table 'cdt_1x_basic' field informat

  • [Microsoft SQL Server 2008 Technology Insider: T-SQL Language Fundamentals] 3. Table Expressions 2010-03-15

    sql2008 t-sql Sql Server table expression of four Derived form: derived table. From clause nested subqueries View: view Common Table Expressions: CTE, common table expression Inline table-valued function: intline TVF, inline table-valued function Com

  • SQL Server 2008 Configuration system failed to initialize 0x84B10001 2010-11-20

    Will be C: \ WINDOWS \ Microsoft.NET \ Framework \ v2.0.50727 \ CONFIG machine.config under the current directory replaced machine.config.default https: / / connect.microsoft.com/SQLServer/feedback/details/381293/sql-express-2008-configuration-system

  • SQL Server 2005 for a table creation time 2010-07-13

    In SQL Server 2005 database, you want to get a table creation time, today I encountered in Baidu Q & A question. Originally thought can not answer them, and think of the Internet Zhao Zhao, and see for yourself, really finally found a solution to the

  • single table into sql server. Export 2011-04-21

    Method One, sql server single table import, export (via CSV file) Export: Open Query Analyzer directly query the information you want to export the table (select * from table), the result Select, Save as xxx.csv file (to get all the information in th

  • SQL Server temporary tables and table variables in the difference between 2010-06-07

    We use a table in the database when the two met often use the table method, namely, the use of temporary tables and table variables. In actual use, we how flexible the use of them in the storage process, although they achieve essentially the same fun

  • SQL Server temporary tables and table variables in the difference 2011-04-20

    Reproduced: http://hi.baidu.com/windy8848/blog/item/06254a4ad6f2982909f7ef0d.html We are in the database using the table, the table will always be used in two ways, respectively, is to use temporary tables and table variables. In actual use, flexible

  • How to copy SQL Server data in the table across server 2011-05-19

    Database replication between different methods of data tables: Target table if the table exists: purpose of the database table insert into .. select * from source database tables .. When the target table does not exist: select * into the destination

  • SQL Server 2008 Management Sudio can not save changes to the structure of the table to solve the problem 2011-02-16

    Today, installed SQL Server 2008 Enterprise Evaluation Edition, the created a table, you want to change the table design, but in the following dialog box pops up when you save *************** Picture in the attachment Later in the "Tools" menu &

  • SQL Server database password associated sa solution to the problem 2010-03-09

    Query Analyzer, connect, authenticate using the "use windows authentication" Then, run: EXEC sp_password NULL, 'NewPassword', 'Sa' Enterprise Manager -> Instance Name -> Security -> Login -> Double-click to the right of SA -> Chan

  • [Microsoft SQL Server 2008 Technology Insider: T-SQL Language Fundamentals] 2. Query articles 2010-03-11

    sql2008 t-sql Single-table queries TOP Options 1. (Combined order by) returns a fixed sequence of cursor 2. You can use the percent keyword 3. Additional attributes tiebreaker: In the absence of a given order of all columns of the order by the time,

  • With SQL Server skills to learn DB2 UDB V8 2010-05-12

    With SQL Server skills to learn DB2 UDB V8 Introduction In today's information technology field, new information continues to impact us - the new software product, new versions and new features. So you can keep up with these constant changes of the h

  • MS SQL Server Query Optimization 2010-06-09

    MS SQL Server Query Optimization Study: xmllover 2007-11-29 Query slow for many reasons, as several common 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formati

  • Comparison of SQL server and Oracle Developer 2010-06-21

    Conceptual distinction between 1.Oracle is an object-relational database management system (ORDBMS), and the Sql server is a relational database management Management System (RDBMS). 2.Oracle use Internet file system, Java-based application that allo

  • SQL Server database solutions sa password 2010-07-19

    Query Analyzer, connect, authenticate using the "use windows authentication" Then, run: EXEC sp_password NULL, 'NewPassword', 'Sa' Enterprise Manager -> Instance Name -> Security -> Login -> Double-click to the right of SA -> Chan

  • 50 Ways to clever optimization of SQL Server database 2010-08-17

    Query slow for many reasons, common following categories: 1, no index or index is not used (this is the most common problems slow query is programming defects) 2, I / O throughput is small, the formation of a bottleneck effect. 3, does not create a c

  • Method of SQL Server to export import data 2010-09-10

    First, export the database into a SQL Server in 1. In SQL Server Enterprise Manager in the database to be transferred is selected, press the mouse button, select All Tasks -> Backup Database. 2. Select the database backup - full, Purpose of the backu

  • SQL Server database to improve the efficiency of commonly used methods 2011-07-14

    No index or index is not used (this is the most common query slow problem is programming defects) 2, I / O throughput is small, the formation of a bottleneck effect. 3, did not create a calculated column does not result in query optimization. 4, memo

  • SQL Server to take the current time, time change 2010-12-27

    getdate / / get the current system date datepart / / get the date specified part (year, month, day, hour table) getdate () function: get the current system date and time. Return value of datetime type. Usage: getdate () Example: select getdate () as