c mysql 是否存在库_判断数据库中表是否存在，并创建

//SQLServer 2000

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[" + tableName + "]') AND type in (N'U')) "

+ " select top 0 * into " + tableName + " from FLOW_LOG; ";

//SQLServer 2008

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + tableName + "]') AND type in (N'U')) "

+ " select top 0 * into " + tableName + " from FLOW_LOG; ";

sysobjects, sys.objects

Contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

name

sysname

Object name.

Id

int

Object identification number.

xtype

char(2)

Object type. Can be one of these object types:

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

L = Log

FN = Scalar function

IF = Inlined table-function

P = Stored procedure

PK = PRIMARY KEY constraint (type is K)

RF = Replication filter stored procedure

S = System table

TF = Table function

TR = Trigger

U = User table

UQ = UNIQUE constraint (type is K)

V = View

X = Extended stored procedure

uid

smallint

User ID of owner object.

list all the objects of any type of interest using the following:

SELECT * FROM sysobjects WHERE xtype =

In the special case of triggers, which are owned by their parent table, you might want to interrogate the database using a self-join, like this:

SELECT

Sys2.[name] TableName,

Sys1.[name] TriggerName,

CASE

WHEN Sys1.deltrig > 0 THEN'Delete'

WHEN Sys1.instrig > 0 THEN'Insert'

WHEN Sys1.updtrig > 0 THEN'Update'

END'TriggerType'

FROM

sysobjects Sys1 JOIN sysobjects Sys2 ON Sys1.parent_obj = Sys2.[id]

WHERE Sys1.xtype='TR'

ORDERBY TableName

sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of foreign key, name of the table it FK belongs and the schema owner name of table.

USEAdventureWorks;GOSELECTnameASObjectName,OBJECT_NAME(schema_id)SchemaName,OBJECT_NAME(parent_object_id)ParentObjectName,name, *FROMsys.objectsWHEREtype=‘F’GO

 Permissions

In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

 Examples

A. Returning all the objects that have been modified in the last N days

USE ;

GO

SELECT name AS object_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

,create_date

,modify_date

FROM sys.objects

WHERE modify_date > GETDATE() - ORDER BY modify_date;

GO

B. Returning the parameters for a specified stored procedure or function

USE ;

GO

SELECT SCHEMA_NAME(schema_id) AS schema_name

,o.name AS object_name

,o.type_desc

,p.parameter_id

,p.name AS parameter_name

,TYPE_NAME(p.user_type_id) AS parameter_type

,p.max_length

,p.precision

,p.scale

,p.is_output

FROM sys.objects ASo

INNER JOINsys.parametersASp ON o.object_id = p.object_id

WHEREo.object_id = OBJECT_ID('')ORDER BY schema_name, o.object_name, p.parameter_id;

GO

C. Returning all the user-defined functions in a database

USE ;

GO

SELECT name AS function_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

,create_date

,modify_date

FROM sys.objects

WHERE type_desc LIKE '%FUNCTION%';GO

D. Returning the owner of each object in a schema.

USE ;

GO

SELECT 'OBJECT' AS entity_type

,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name

,name

FROM sys.objects WHERE SCHEMA_NAME(schema_id) = ''

UNION

SELECT 'TYPE' AS entity_type

,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name

,name

FROM sys.types WHERE SCHEMA_NAME(schema_id) = ''

UNION

SELECT 'XML SCHEMA COLLECTION' AS entity_type

,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name

,xsc.name

FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s

ON s.schema_id = xsc.schema_id

WHERE s.name = '';

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_QueryStockWarning]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[P_QueryStockWarning]

GO

的含义：

dbo.sysobjects 为系统数据库master中的系统表sysobjects;

OBJECT_ID：返回数据库对象标识号。N是显式的将非unicode字符转成unicode字符,写作object_id(PerPersonData);

OBJECTPROPERTY：返回当前数据库中对象的有关信息。1表“真”。同样可以写成OBJECTPROPERTY(id, sUserTable) = 1;

if exists (select * from sysobjects where objectproperty(object_id('P_QueryStockWarning'),'isProcedure') = 1)

这句话判断存储过程'P_QueryStockWarning'是否存在，如果存在就删除该存储过程

分享到：

2010-06-16 15:30

浏览 1654

分类:数据库

评论