//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
分类:数据库
评论