Shulou(Shulou.com)05/31 Report--

How to judge the existence of stored procedures in sqlserver? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

How to judge the existence of a table or database in sql server, but in practice, it is necessary to judge Status status bits: some of these status bits can be set by the user using sp_dboption (read only, dbo use only, single user, etc.):

1 = autoclose; uses the sp_dboption setting. The database is completely closed and its resources are released after the last user logs out. 4 = select into/bulkcopy; uses the sp_dboption setting. Allows the use of Select INTO statements and fast bulk copy. 8 = trunc. Log on chkpt; uses the sp_dboption setting. If the database is in log truncation mode, the checkpoint truncates the inactive portion of the log. This option can only be set for master databases. 16 = torn page detection, using the sp_dboption setting. Incomplete pages can be detected. 32 = loading. 64 = pre recovery. 128 = recovering. 256 = not recovered. 512 = offline; uses sp_dboption settings. The database will be offline. 1024 = read only; uses the sp_dboption setting. The user can only read the data in the database and cannot modify it. 2048 = dbo use only; uses the sp_dboption setting. Only the database owner can use the database. 4096 = single user; uses the sp_dboption setting. Only one user can access the database at a time. 32768 = emergency mode. 4194304 = autoshrink. 1073741824 = cleanly shutdown.

You can open multiple bits at the same time.

For example, to determine whether a database is offlineselect * From master.dbo.sysdatabases where name='pubs' and status512

Determine whether the table object exists in SQL Server: select count (*) from sysobjects where id = object_id ('database name. Owner. Table name')

If exists (select count (*) from sysobjects where id = object_id ('database name. Owner. Table name') print 'exists' elseprint 'does not exist'

SQL Server to determine whether the field exists in the table: if exists (select * from syscolumns where name='colname1' and id=object_id ('database name. Owner. Table name') print 'exists' elseprint 'does not exist' means colname1 field exists in table tablename1 example: select * from syscolumns where name='Test' and id=object_id ('dbo.test')

Determine whether the table object exists in Access: in fact, the Access database also has system tables, which store the object name Select Count (*) AS Qty FROM MSysObjects Where ((MSysObjects.Name) Like 'table name')

The copy code is as follows: whether if exists (select * from master..sysdatabases where name=N' library name') print 'exists'elseprint' not exists'- exists in the library-determine whether there is if exists in the table name to be created (select * from dbo.sysobjects where id = object_id (N' [dbo]). [table name]') and OBJECTPROPERTY (id, NabilisUserTable') = 1)-Delete table drop table [dbo]. [table name] GO-whether IF COL_LENGTH exists in the column ('table name') 'column name') IS NULL PRINT 'not exists'ELSEPRINT' exists'alter table table name drop constraint default name go alter table table name drop column column name go-determine whether to create temporary table If Object_Id ('Tempdb.dbo.#Test') Is Not NullBeginprint' exist 'EndElseBeginprint' does not exist 'End--determine whether if exists in the stored procedure name to be created Exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [stored procedure name]') and OBJECTPROPERTY (id, NumbIsProcedure') = 1)-Delete stored procedure drop procedure [dbo]. [stored procedure name] GO-determines whether if exists exists in the view name to be created (select * from dbo.sysobjects where id = object_id (N' [dbo]. [view name]') and OBJECTPROPERTY (id, Noble IsView') = 1)-Delete view drop view [dbo]. [view name] GO-determine whether if exists (select * from sysobjects where xtype='fn' and name=' function name') if exists (select * from dbo.sysobjects where id = object_id (N' [dbo]) exists in the function name to be created. [function name]') and xtype in (drop function FNN, NFN, NFF')-Delete the function drop function [dbo]. [function name] GO if col_length ('table name', 'column name') is nullprint 'does not exist' select 1 from sysobjects where id in (select id from syscolumns where name=' column name') and name=' table name'

After reading the above, have you mastered the method of how to determine the existence of stored procedures in sqlserver? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.