SQL server determines whether a database, table, column, or view exists. SQL server

SQL server determines whether a database, table, column, or view exists. SQL server

1. Determine whether the database exists

If exists (select * from sys. databases where name = 'database name ')
Drop database [database name]

2. Check whether the table exists.

If exists (select * from sysobjects where id = object_id (N '[Table name]') and OBJECTPROPERTY (id, N 'isusertable') = 1)
Drop table [table name]

3. Determine whether a stored procedure exists

If exists (select * from sysobjects where id = object_id (n' [stored procedure name] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1)
Drop procedure [stored procedure name]

4. Determine whether a temporary table exists

If object_id ('tempdb .. # temporary table name') is not null
Drop table # temporary table name

5. Determine whether a view exists

-- Determine whether the attempt 'myview52' exists
If exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N 'myview52 ')
PRINT 'exist'
Else
PRINT 'nonexistent'

6. Determine whether a function exists.

-- Determine whether the name of the function to be created exists
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [function name] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [function name]

7. obtain information about the object created by the user

SELECT [name], [id], crdate FROM sysobjects where xtype = 'U'

8. Determine whether a Column exists

If exists (select * from syscolumns where id = object_id ('table name') and name = 'column name ')
Alter table Name drop column name

9. Determine whether the column is an auto-incrementing column.

If columnproperty (object_id ('table'), 'col', 'isidentity ') = 1
Print 'auto-incrementing column'
Else
Print 'not auto-incrementing column'

SELECT * FROM sys. columns WHERE object_id = OBJECT_ID ('table name') AND is_identity = 1

10. Check whether an index exists in the table.

If exists (select * from sysindexes where id = object_id ('table name') and name = 'index name ')
Print 'exist'
Else
Print 'nonexistent'

Delete index drop index table name. index name

Or: drop index name on table name (it seems that 2000 does not work)

11. view objects in the database

SELECT * FROM sys. sysobjects WHERE name = 'object name' SELECT * FROM sys. sysobjects WHERE name = 'object name'

How can I view fields in the view in the SQL Server2005 database?

1. In SQLSERVER manager, right-click the View Graph and choose design. On the design screen, you can see the column, source table, and logic.
2. Select SQLSERVER manager and click the plus sign (+) to expand the chart.
3. Use the SQL statement, select * from view name, and you can see the column in the result
The three methods upstairs are both direct and correct.
Querying View fields using SQL
Select name from syscolumns where id = object_id ('view name ')
In this way, the data is the fields of the view.

How does SQL server determine whether a view exists?

SELECT count (*) as cut FROM sysviews WHERE object_id = '[dbo]. [view name]'
If fieldByName ('cut ')> 0 then
// Exists
Else
// Does not exist