1. Get a list of a table, such as the column of BRWPLANH SELECT * from syscolumns where [id] = (select [id] from sysobjects where [name] = 'brwlanh')
2. Delete the stored procedure of all tables in the database - ********************************************************************************* *******************************************
- This program will delete all tables in the database, please use it carefully! If this program brings you everything
- The consequences of inconvenience, I don't assume any responsibility!
- Author: sillywxj (superwxj)
- Date: 2005-07-02
- *************** * ********************************* ***********************************
IF exists (select * from dbo.sysobjects where id = Object_id (n '[dbo]. [UP_DROPALLTABLE]') And ObjectProperty (ID, n'isprocedure ') = 1)
Drop Procedure [DBO]. [up_dropalltable]
Go
CREATE Procedure Up_DropAllTable
With encryption
AS
Begin
Declare @Mydropstr Varchar (300) - Delete statement to be made
Declare @Mytablename VARCHAR (256) - Name
DECLARE cursor_Table cursor for select [name] from sysobjects where [Xtype] = 'U' OPEN cursor_Table FETCH NEXT FROM cursor_Table INTO @myTableName while @@ FETCH_STATUS = 0 BEGIN set @myDropStr = 'Drop table [' @ myTableName ']'
Execute sp_executesql @Mydropstr
Fetch next from cursoor_table @mytablename endocate cursor_table deallocate cursor_table
End
3. Delete all the data in the table in the database
--*************************statement********************** ********************* - This program will delete data on all tables in the database, please use it carefully! If this program gives you everything - inconvenient consequences, we shall have no liability - author:! sillywxj (superwxj) - date: 2005-07-02-- ***************** ******* * ************************************************** ******** if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [up_dropallTableData]) And ObjectProperty (ID, n'isprocedure') = 1) DROP Procedure [DBO .] [up_DropAllTableData] GOCREATE PROCEDURE up_DropAllTableDataWITH ENCRYPTIONASBEGIN DEclare @myDropStr varchar (300) - to spell delete statement Declare @myTableName varchar (256) - table name DECLARE cursor_Table cursor for select [name] from sysobjects where [xtype] = 'U' Open Cursor_Table Fetch Next from Cursor_Table INTO @Mytablename While @@ fetch_status = 0 Begin Set @Mydropstr = 'Delete from [' @ MyTableName ']'
Execute sp_executesql @Mydropstr
Fetch next from cursoor_table @mytablename endocate cursor_table deallocate cursor_table
End
4, find the NULL value in the database
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [up_ShowNull]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [up_ShowNull] GOCREATE PROCEDURE up_ShowNullWITH Encryptionasbeginset NoCount on Declare @Myselectstr NVARCHAR (400) Declare @Mytablename VARCHAR (256) - Table name Declare @myfield varchar (256) - Field Declare @ncount int - Get the number of rows
DECLARE cursor_uTable cursor for select [name] from sysobjects where [Xtype] = 'U' OPEN cursor_uTable FETCH NEXT FROM cursor_uTable INTO @myTableName while @@ FETCH_STATUS = 0 BEGIN Declare cur_AllField CURSOR FOR SELECT [NAME] FROM sysColumns where [ID] = ( SELECT [ID] FROM sysobjects WHERE [NAME] = @ myTableName) OPEN cur_AllField FETCH NEXT FROM cur_AllField Into @myField WHILE @@ FETCH_STATUS = 0 BEGIN set @mySelectStr = 'SELECT @ nCount = Count (*) FROM' @myTableName ' WHERE ' @ myField ' IS NULL 'EXECUTE sP_EXECUTESQL @ mySelectStr, N' @ nCount int out ', @ nCount out IF @nCount> 0 BEGIN print' table: ' @ myTableName ', the fields: ' @ myField ' 'print @MYSELECTSTR END FETCH NEXT from Cur_allfield INTO @MYFIELD END CLOSE CUR_ALLFIELD DEALLOCATE CUR_ALLFIELDFETCH NEXT from Cursor_utable INTO @Mytablename End Close Cursor_utable deallocate Cursor_utable
Set NoCount Offend
