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

//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 = <type of interest>

• 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.
USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE type = ‘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 <database_name>;
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() - <n_days>
ORDER BY modify_date;
GO

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

USE <database_name>;
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 AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

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

USE <database_name>;
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 <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
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) = '<schema_name>'
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 = '<schema_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'是否存在，如果存在就删除该存储过程