Sqlserver 常用 SQL

1.Batch update top 10

2.Backup table

3.Add/Alter column and PK

4.Create/Replace Index

5.Dynamic Create/Replace sequence

6.Create/Replace Stored Procedure

7.Create/Replace function

8.Delete duplicate records and remainder 1 row

9.Dynamic generate ddl for drop/create PK

10.Query dependents of objects

11.Qeury table info

12.Sybase query PK

 

-- 1.Batch update top 10
update top(10)  tablename set id=1 where id is null

-- 2.Backup table
select * into new_table from old_table

-- 3.Add/Alter column and PK
ALTER TABLE table_name DROP CONSTRAINT pk_name
ALTER TABLE table_name ALTER COLUMN name_no char(2) not null
ALTER TABLE table_name ADD country char(10) not null
ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY CLUSTERED (id, name)

-- 4.Create/Replace Index
IF EXISTS (
       SELECT * FROM sys.objects  so JOIN sys.indexes si
       ON so.object_id = si.object_id
       JOIN sys.schemas sc
       ON so.schema_id = sc.schema_id
       WHERE so.name = N'table_name'  AND sc.name = N'schema'  AND si.name = N'index_name' AND so.type in (N'U'))
   DROP INDEX [index_name] ON [schema].[table_name] 
GO
CREATE NONCLUSTERED INDEX [index_name] ON [schema].[table_name]
(
   [id] ASC,
   [name] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] 
GO

-- 5.Dynamic Create/Replace sequence
IF EXISTS(SELECT * FROM sys.sequences WHERE name = N'sequence_name')
  DROP SEQUENCE schema.sequence_name;
DECLARE @start_value varchar(20),@create_seq_sql nvarchar(2000)
SELECT @start_value = max(id) FROM shema.table_name WHERE id IS NOT NULL
IF @start_value IS NULL
   SET @start_value=0
SELECT @create_seq_sql = '
CREATE SEQUENCE schema.sequence_name  
  AS bigint
    START WITH  ' + @start_value + ' 
    INCREMENT BY 1'
EXEC sp_executesql @create_seq_sql
GO

-- 6.Create/Replace Stored Procedure
IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[schema].[sp_name]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [schema].[sp_name]
GO
CREATE PROCEDURE [schema].[sp_name]
  @param_1 numeric(20, 0),
  @param_2 varchar(10)
AS 
  BEGIN
    -- business logic
  END
GO

-- 7.Create/Replace function
IF EXISTS(SELECT * FROM sys.sysobjects WHERE id = object_id(N'schema.function_name') and xtype='FN')
    DROP FUNCTION [schema].[function_name]
GO
CREATE function [schema].[function_name](
    @param NVARCHAR(1000)
) 
returns NVARCHAR(1000) 
as 
begin
    -- bisiness logic
    return ''
end
go

 

-- 8.Delete duplicate records and remainder 1 row
DECLARE 
    @count int,
    @field_1 char(10),
    @field_2 char(8),
    @field_3 char(2),
    @field_4 decimal(2, 0),
    @field_5 char(10),
    @field_6 char(3)
DECLARE
    cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR 
    select count(1),field_1,field_2,field_3,field_4,field_5,field_6 
    from [schema].[table_name] -- update table name
    group by field_1,field_2,field_3,field_4,field_5,field_6
    having count(1) > 1 

    OPEN cursor_name
    FETCH cursor_name
        INTO @count,@field_1,@field_2,@field_3,@field_4,@field_5,@field_6
    WHILE (@@FETCH_STATUS = 0)    
        BEGIN
        delete top (@count-1) 
        from [schema].[table_name] -- update table name
        where field_1=@field_1 and field_2=@field_2 
        and field_3=@field_3 and field_4=@field_4 and field_5=@field_5 and field_6=@field_6
        FETCH cursor_name
            INTO @count,@field_1,@field_2,@field_3,@field_4,@field_5,@field_6
        END
    CLOSE cursor_name
    DEALLOCATE cursor_name

 

-- 9.Dynamic generate ddl for drop/create PK
declare
 @objname nvarchar(776)

 if  (object_id('tmpdb.dbo.#temp_table') IS null)
    drop table #temp_table 

  CREATE  TABLE #temp_table  
 (  
 table_name   nvarchar(256)
  ,cnst_id   int   NOT NULL  
  ,cnst_type   nvarchar(256)  NOT NULL   -- 128 for name + text for DEFAULT  
  ,cnst_name   sysname   NOT NULL  
  ,cnst_nonblank_name sysname   NOT NULL  
  ,cnst_2type   character(2)  NULL  
  ,cnst_disabled  bit    NULL  
  ,cnst_notrepl  bit    NULL  
  ,cnst_del_action  int    NULL  
  ,cnst_upd_action  int    NULL  
  ,cnst_keys   nvarchar(2126)  NULL -- see @keys above for length descr  
  
 ) 
declare tablename_cursor cursor local static for
select schema_name(schema_id)+'.'+name as 'tablename' from sys.objects where type='U'
--######################
-- set params
and name in ('table_name')
-- and schema_name(schema_id) = 'schema_name'
--#####################
 for read only 
 open tablename_cursor  
 fetch tablename_cursor into @objname  
 while @@fetch_status >= 0  
 begin

 declare

  @objid   int           -- the object id of the table  
   ,@cnstdes  nvarchar(4000)-- string to build up index desc  
   ,@cnstname  sysname       -- name of const. currently under consideration  
   ,@i    int  
   ,@cnstid  int  
   ,@cnsttype  character(2)  
   ,@keys   nvarchar(2126) --Length (16*max_identifierLength)+(15*2)+(16*3)  
   ,@dbname  sysname   

  
 -- Check to see if the table exists and initialize @objid.  
 select @objid = object_id(@objname)  
  
 -- STATIC CURSOR OVER THE TABLE'S CONSTRAINTS  
 declare ms_crs_cnst cursor local static for  
  select object_id, type, name from sys.objects where parent_object_id = @objid  
   and type ='PK' 
  for read only  

 -- Now check out each constraint, figure out its type and keys and  
 -- save the info in a temporary table that we'll print out at the end.  
 open ms_crs_cnst  
 fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname  
 while @@fetch_status >= 0  
 begin  
  

  begin  
   -- get indid and index description  
   declare @indid smallint  
   select @indid = i.index_id,  
     @cnstdes = case when @cnsttype = 'PK'  
        then 'PRIMARY KEY' else 'UNIQUE' end  
        + case when index_id = 1  
        then ' (clustered)' else ' (non-clustered)' end  
   from  sys.indexes i join  
      sys.key_constraints k on  
       (  
       k.parent_object_id = i.object_id and k.unique_index_id = i.index_id  
       )  
   where i.object_id = @objid and k.object_id = @cnstid  
  
   -- Format keys string  
   declare @thiskey nvarchar(131) -- 128+3  
  
   select @keys = index_col(@objname, @indid, 1), @i = 2  
   if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)  
    select @keys = @keys  + '(-)'  
  
   select @thiskey = index_col(@objname, @indid, @i)  
   if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))  
    select @thiskey = @thiskey + '(-)'  
  
   while (@thiskey is not null)  
   begin  
    select @keys = @keys + ', ' + @thiskey, @i = @i + 1  
    select @thiskey = index_col(@objname, @indid, @i)  
    if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))  
     select @thiskey = @thiskey + '(-)'  
   end  
  
   -- ADD TO TABLE  
    -- ADD TO TABLE  
   insert into #temp_table  
    (cnst_id,cnst_type,cnst_name, cnst_nonblank_name,cnst_keys, cnst_2type, table_name)  
   values (@objid, @cnstdes, @cnstname, @cnstname, @keys, @cnsttype, @objname)

    end
    fetch ms_crs_cnst into @cnstid ,@cnsttype ,@cnstname  
end

 deallocate ms_crs_cnst  
fetch tablename_cursor into @objname  
end
 deallocate tablename_cursor  

 select * from #temp_table 


 select 'ALTER TABLE '+table_name+' DROP CONSTRAINT '+cnst_name from #temp_table
 select 'ALTER TABLE '+table_name+' ADD CONSTRAINT '+cnst_name+' PRIMARY KEY ('+cnst_keys+')' from #temp_table

 

-- 10.Query dependents of objects
SELECT  DISTINCT
        procObj.[object_id]     AS [ProcObjectId],
        procSchema.[name]       AS [ProcSchema],
        procObj.[Name]          AS [ProcName],
        tableObj.[object_id]    AS [TableObjectId],
        tableSchema.[name]      AS [TableSchema],
        tableObj.[Name]         AS [TableName]
FROM sys.sql_dependencies AS dep
INNER JOIN sys.objects AS procObj
ON procObj.[object_id] = dep.[object_id]
INNER JOIN sys.schemas AS procSchema
ON procSchema.[schema_id] = procObj.[schema_id]
INNER JOIN sys.objects AS tableObj
ON tableObj.[object_id] = dep.[referenced_major_id]
INNER JOIN sys.schemas AS tableSchema
ON tableSchema.[schema_id] = tableObj.[schema_id]
WHERE   procObj.[type] = 'P'
    -- using this filter we can control dependent object types
    -- e.g. tableObj.[type] IN ('U') - returns tables only
    AND tableObj.[type] IN ('V', 'U')
    AND procObj.[object_id] in (object_id('schema.table_name'))

 

-- 11.Qeury table info
use schema;
select
    d.name as 'Table',
    a.name as 'Field',
    b.name as 'Type',
    a.length as 'Length',
    (case  when  a.status =8  then 'null'  else 'not mull'  end) as  'Nullable',
    isnull(e.text,'')  as 'Default'
FROM syscolumns     a left join systypes   b
    on     a.usertype=b.usertype
    inner join sysobjects   d
    on   a.id=d.id and d.type='U' and d.name<>'dtproperties'
    left join syscomments   e
    on   a.cdefault=e.id
where d.name='table_name'
order by d.name,a.colid

 

-- 12.Sybase query PK
SELECT * FROM (
SELECT O.name as "Table_Name",
            C.name as "Column",
            C.length as "Length",
            C.status as "Allow Null",
            ISNULL((select "Y" as PK_FLAG from syskeys K where O.id=K.id
            and K.type = 1 and (C.colid =K.key1 or C.colid =K.key2 OR C.colid =K.key3  OR C.colid =K.key4 OR C.colid =K.key5 OR C.colid =K.key6 OR C.colid =K.key7 Or C.colid =K.key8 )),"N") as Primary_Key_Flag,
            C.colid as colid
FROM        sysobjects O,            
            syscolumns C
WHERE     O.id = C.id
AND       O.type = "U"             -- user tables only
AND       O.name in ('table_name')
--ORDER BY  O.name, C.colid
) AS tem
where tem.Primary_Key_Flag='Y'
ORDER BY  tem.Table_Name, tem.colid