SQL语句集锦（逐步添加）

1、得到一个表的列信息，譬如表brWPlanh的列信息
select  * from sysColumns where [id]=(select [id] from sysobjects where  [name]='brWPlanh')

2、删除数据库中的所有表的存储过程

--*************************声明***********************************************
--本程序将删除数据库中的所有表,请谨慎使用!若本程序给您带来一切
--不便的后果,本人均不承担任何责任!
--作者:sillywxj(superwxj)
--日期:2005-07-02
--*************************END*************************************************
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)    --要拼成的删除语句
    Declare @myTableName varchar(256)  --表名

    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 cursor_Table INTO  @myTableName        
      End
    CLOSE cursor_Table      
    DEALLOCATE cursor_Table

END

3、删除数据库中表的所有数据

--*************************声明***********************************************
--本程序将删除数据库中的所有表的数据,请谨慎使用!若本程序给您带来一切
--不便的后果,本人均不承担任何责任!
--作者:sillywxj(superwxj)
--日期:2005-07-02
--*************************END*************************************************
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]
GO
CREATE PROCEDURE up_DropAllTableData
WITH ENCRYPTION
AS
BEGIN
    DEclare @myDropStr varchar(300)    --要拼成的删除语句
    Declare @myTableName varchar(256)  --表名

    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 cursor_Table INTO  @myTableName       
      End
    CLOSE cursor_Table      
    DEALLOCATE cursor_Table

END

4、查找数据库中的null值

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]
GO
CREATE PROCEDURE up_ShowNull
WITH ENCRYPTION
AS
BEGIN
Set NoCount ON
    DEclare @mySelectStr nvarchar(400)   
    Declare @myTableName varchar(256)  --表名
    Declare @myField     varchar(256)  --字段
    Declare @nCount      int           --获取存在的行数

    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 '表:'+@myTableName+',字段:'+@myField+''
                print @mySelectStr
              END
              FETCH NEXT FROM cur_AllField Into @myField
          END
          CLOSE cur_AllField
          DEALLOCATE cur_AllField

          FETCH NEXT FROM cursor_uTable INTO  @myTableName       
    End
    CLOSE cursor_uTable      
    DEALLOCATE cursor_uTable

Set noCount OFF
END