Excerpt from :http://www.cnblogs.com/bugY/archive/2011/09/21/2184182.html

About SQL Server All the information of the database is stored in its system table . I wonder if you spend too much time checking system forms , Because you're always busy with user forms . however , You may need to do something unusual once in a while , For example, all the triggers in the database . You can check the forms one by one , But if you have 500 If you have a form , It can take a lot of labor .

This makes sysobjects Forms have a place . Although I don't recommend updating this form , But of course you have the right to censor it .

in the majority of cases , The two columns that are most useful to you are sysobjects.name and sysobjects.xtype. The first one is used to list the names of the objects to be investigated , The second one defines the type of the object , It can be one of the following object types :

   C = CHECK constraint  
   D = Default value or DEFAULT constraint  
   F = FOREIGN KEY constraint  
   L = journal  
   FN = Scalar function  
   IF = Embedded table functions  
   P = stored procedure  
   PK = PRIMARY KEY constraint ( The type is K) 
   RF = Copy filter stored procedure  
   S = The system tables  
   TF = Table function  
   TR = trigger  
   U = User table  
   UQ = UNIQUE constraint ( The type is K) 
   V = View  
   X = Extended stored procedure  

In the case of a trigger , The other three columns used to identify the trigger type are :deltrig、instrig and uptrig.

You can use the following command to list all the objects of interest :

SELECT * FROM sysobjects WHERE xtype = <type of interest>

select * from sys.objects -- Each user-defined schema scope object created in the database corresponds to a row in the table .

select * from sys.sysobjects-- Every object created in the database ( Constraints, for example 、 The default value is 、 journal 、 Rules and stored procedures ) They all correspond to one line .

"sys.objects yes sys.sysobjects Subset "

Add :

From :http://hi.baidu.com/pgzdskwfxbcdiwr/item/d9b22e91b0552bdc7a7f0151

Determine whether a table already exists in the database , Delete the table if there are any

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ Table name ]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ Table name ]


if exists (select * from sysobjects where id = object_id(N' Table name ') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ Table name ]


if(Exists(Select * From SysObjects Where xtype='U' And Name=' Table name ')) 
drop table [dbo].[ Table name ]

1、 ask :object_id(N' Table name ') in N' What does it mean

answer :N'' representative Unicode type . It can support object names in different languages

2、 ask :select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[usertab]')   and   OBJECTPROPERTY(id,   N'IsUserTable')   =   1   
In this sentence object_id(N'[dbo].[usertab]') and OBJECTPROPERTY(id,   N'IsUserTable')   =   1   
What does that mean? ?

answer :object_id(N'[dbo].[usertab]'): Is to get the system to the table usertab The only distribution ID   
OBJECTPROPERTY(id,   N'IsUserTable')   =   1   
The properties of this object are of table type  objectproperty(id,property) Use of functions ,

3、 ask :USE master SELECT * FROM ...SysObjects ” ...sysobjects ” -- What does the prefix of three periods mean ?

answer : For database object names Transact-SQL A reference can be a four part name , The format is as follows :[ server_name.[[database_name].[owner_name]. | database_name.[owner_name]. | owner_name.] ] object_name

more :


Learn more :

object_id() and objectproperty() It's all system functions :

object_id(): Returns the object identification number . Parameters : Object name (nvarchar(128)). return integer
objectproperty(): Returns the property value of an object property . Parameters : object ID(integer), The name of the zodiac (varchar). return integer

It can be downloaded from MSDN Find information on :



N: Express UNICODE type , It can support object names in different languages

It can be downloaded from “ Link manager ” I found it there :

In the connection manager, click “ Programmability ”-》“ function ”-》“ System function ”-》“ Metadata functions ”, You can find object_id and OBJECTPROPERTY

posted @ 2012-11-18 16:53 KeenLeung read (815) Comment on (0) edit Collection


SQL Server Medium sysobjects More articles about

  1. SQL Server Query database and table information statements

    /* -- This document mainly summarizes Microsoft SQL Server In the database and table related information query statement . -- Two query methods are generally given in the following query statements , -- A Method to access the system table , Adapt to SQL 20 ...

  2. SQL Server Transaction and lock in

    Understand transactions and locks Business : Keep logical data consistent and recoverable , A necessary weapon . lock : When multiple users access the same database resource , A mechanism for access priority and authority management , Without him, things would be a mess , It can't guarantee the safe and correct reading and writing of data . Deadlock : ...

  3. MS SQL Server In the data table 、 View 、 function / Method 、 Judge and create whether stored procedure exists

    Preface In the operation of the database, often used to determine the data table . View . function / Method . Whether stored procedures exist , If exist , You need to delete it and recreate it . Here are MS SQL Server Sample code in . Data sheet (Table) When you create a data table ...

  4. SQL Server in Text and varchar(max) Data type differences

    SQL Server in Text and varchar(max) Data type differences   I used to only know text and image It could be SQL Server Obsolete data types , But the exact reason is not clear , When I was reading today, I found text And v ...

  5. SQL Server Query the number of tables in a database


  6. Show Sql Server Information about all tables or rows in a table

    stay MSSQL Displays information about all tables or views in a database : ( The following statement is to get all table information , Put the green words "U" Replace with "V" Then get all view information .) SELECT sysobjec ...

  7. SQL Server Solve deadlocks in

    SQL Server A new method to solve deadlock in Deadlock of database operation is inevitable , This article is not intended to discuss how deadlocks occur , The point is to solve deadlocks , adopt SQL Server 2005, Now there seems to be a new solution . The following ...

  8. MS SQL SERVER System table in

    MS SQL SERVER System table in Serial number name explain remarks 1 syscolumns Each column in each table and view occupies one row in the table , Each parameter in the stored procedure also occupies a row in the table .   2 syscomments Include every ...

  9. from SQL Server Import / export Excel Basic approach ( turn )

    from sql server Import / export excel Basic approach /*=========== Import / export excel Basic approach ===========*/ from excel In the document , Import data to sql In the database , very ...

Random recommendation

  1. Slave2: no datanode to stop(HADOOP_PID_DIR)

    HADOOP_PID_DIR I wanted to set... In the environment variable , Use it directly in relevant documents , But I remember at that time JAVA_HOME Neither. , Let's set it one by one . occasionally , We're interested in running for days or months hadoop perhaps hbas ...

  2. WPS Text beats automatically when typing in a form

    You can set the properties of the table to achieve . 1. Select table , Right click , choice “ Table properties ” 2. In the dialog box that appears , Text surround selection “ nothing ”,“ That's ok ” The Settings for “ Allow line breaks across pages ”, That's all right. .

  3. each function

  4. access 2007 vba ( What do you think )

    OpenReport Method execution OpenReport Operation in Visual Basic in . grammar   expression  .OpenReport(ReportName, View, FilterName, Whe ...

  5. 2014 The second question of Baidu star qualification competition Disk Schedule

    Problem Description There is a lot of need to read data from disk , Include sequential reads . Random reads . In order to improve efficiency , It is necessary to arrange disk reading manually . However , In reality , It's very complicated . Let's consider a relatively simple scenario . ...

  6. blog The opening

    It was written java Learn the opening , Now change it to blog opening . In fact, I always want to record my learning process , Or an excuse , Have been busy , From Chongqing to Guangzhou , From Guangzhou to Tianjin again , Another new stage has begun , It's all the time , ha-ha ...

  7. How to generate the complex data regularly to Ministry of Transport of P.R.C by DB Query Analyzer

    How to generate the complex data regularly to Ministry of Transport of P.R.C by DB Query Analyzer 1 ...

  8. Take a look back. shell Script 1

    First : 1. Write a hello.sh The program hello.sh vim hello.sh #!/bin/sh # author :yangyx echo "hello shell"</sp ...

  9. Use adb see CPU And memory

    adb shell ->cat/sys/class/net/wlan0/address obtain Mac Address abd shell –>cat /proc/cpuinfo obtain CPU Information adb ...

  10. English trip -- Review Unit3 Family family

    Words daughter grandfather grandmother husband wife uncle aunt brother sister Who is ...? Loki's ... ...