sql,Code片段,代码实例,代码示例,代码片段库

sql 获得oracle限制

获得oracle限制

new_gist_file.sql

select
  const.name as constraint_name,
  rconst.name as ref_constraint_name,
  obj.name as table_name,
  coalesce(acol.name, col.name) as column_name,
  robj.name as ref_table_name
from
  sys.con$ const
inner join sys.cdef$ cdef
  on cdef.con# = const.con#
inner join sys.ccol$ ccol
  on ccol.con# = const.con#
inner join sys.col$ col
  on (ccol.obj# = col.obj#) and (ccol.intcol# = col.intcol#)
inner join sys.obj$ obj
  on ccol.obj# = obj.obj#
left join sys.attrcol$ acol
  on (ccol.obj# = acol.obj#) and (ccol.intcol# = acol.intcol#)
inner join sys.con$ rconst
  on rconst.con# = cdef.rcon#
inner join sys.ccol$ rccol
  on rccol.con# = rconst.con#
inner join sys.col$ rcol
  on (rccol.obj# = rcol.obj#) and (rccol.intcol# = rcol.intcol#)
inner join sys.obj$ robj
  on rccol.obj# = robj.obj#
left join sys.attrcol$ racol
  on (rccol.obj# = racol.obj#) and (rccol.intcol# = racol.intcol#)
where robj.name = 'TABLE_NAME';

sql 更改Oracle序列

更改Oracle序列

new_gist_file.sql

declare
	v_seq_next_id number(10);
	v_max_table_id number(10);
	v_diff number(10);
begin
	select eqw_rdb.seq_configuration_template.nextval INTO v_seq_next_id FROM dual;
	select max(eqw_rdb.configuration_template.id) into v_max_table_id from eqw_rdb.configuration_template;
	v_diff := v_max_table_id - v_seq_next_id;
	execute immediate ('alter sequence eqw_rdb.seq_configuration_template increment by ' || v_diff);
	select eqw_rdb.seq_configuration_template.nextval into v_seq_next_id from dual;
	execute immediate ('alter sequence eqw_rdb.seq_configuration_template increment by ' || 1);
end;

sql oracle作业调度程序日志

oracle作业调度程序日志

new_gist_file.sql

select  log_date, job_name, status  req_start_date, actual_start_date, run_duration 
from  dba_scheduler_job_run_details 
where job_name = 'MYJOB';

sql Dynamicwebsøgog erstat i afsnit

Dynamicwebsøgog erstat i afsnit

DW-soeg-erstat.sql

/*SELECT REPLACE(CAST(ParagraphText AS varchar(max)),'<<span','<span') AS NewText FROM Paragraph WHERE ParagraphText LIKE '%<<span%'*/
UPDATE Paragraph SET ParagraphText = REPLACE(CAST(ParagraphText AS varchar(max)), '<o:p></o:p>', '') WHERE 2=2

sql 不知道我在哪里得到这个。这将重建数据库中的所有索引。

不知道我在哪里得到这个。这将重建数据库中的所有索引。

Rebuild All Indexes.sql

SET NOCOUNT ON;
DECLARE @db_id int;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000); 

SET @db_id = DB_ID();
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@db_id, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

sql 从逗号分隔的列表备份一组数据库。需要“StringToTable”函数，该函数也在此列表中。

从逗号分隔的列表备份一组数据库。需要“StringToTable”函数，该函数也在此列表中。

Backup A Set Of Databases.sql

DECLARE @BackThisUp VarChar(100)
DECLARE @RootPath VarChar(1000)
DECLARE @FileName VarChar(256)
DECLARE @TodaysDate VarChar(20)
 
SET @RootPath = 'C:\MyBackups\'
 
DECLARE dbBackupCursor CURSOR FOR
SELECT Value FROM StringToTable('DB1,DB2,DB3,DB4')
OPEN dbBackupCursor
FETCH NEXT FROM dbBackupCursor INTO @BackThisUp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = @RootPath + @BackThisUp + '.BAK'
BACKUP DATABASE @BackThisUp TO DISK = @FileName
FETCH NEXT FROM dbBackupCursor INTO @BackThisUp
END
CLOSE dbBackupCursor
DEALLOCATE dbBackupCursor

sql 创建一个新的MySQL用户并授予所有权限。

创建一个新的MySQL用户并授予所有权限。

new-user.sql

GRANT ALL PRIVILEGES ON %DB_NAME%.* TO %DB_USER%@%DB_HOST% IDENTIFIED BY '%DB_PASSWORD%';

sql Oracle SQL：返回当前月份第一天的字符串（如果其他月份为ar，则替换SYSDATE的任何日期字段/值

Oracle SQL：返回当前月份第一天的字符串（如果需要其他月份，则替换SYSDATE的任何日期字段/值）。

firstDayOfCurrentMonth.sql

--Return the first day of the current month in MM/DD/YYYY format
TO_CHAR(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1),'MM/DD/YYYY')

sql 的PostgreSQLでレコード数とサイズ（物理容量）を调べる

的PostgreSQLでレコード数とサイズ（物理容量）を调べる

count.sql

SELECT
  T2.relname,  
  cast (T2.reltuples as int) as rows,
  (relpages * 8192) as bytes 
 FROM pg_stat_user_tables AS T1 INNER JOIN pg_class AS T2 ON T1.relname = T2.relname 
 ORDER BY T2.relname;

sql 使用这些MySQL查询修复WordPress数据库编码问题...来源＃1：http：//tanyanam.com/technology/fixing-encoding-issues-on-wo

使用这些MySQL查询修复WordPress数据库编码问题...来源＃1：http：//tanyanam.com/technology/fixing-encoding-issues-on-wordpress-comments-display-as-question-markssource＃2：http： //stackoverflow.com/a/3568144/284091 3组重复查询包括帖子内容，帖子标题和帖子。

database encoding fix.sql

ALTER DATABASE 'my_wp_database' CHARACTER SET utf8;
ALTER TABLE 'wp_comments' CHARACTER SET utf8;
alter table 'wp_comments' change comment_content comment_content LONGTEXT CHARACTER SET utf8;
alter table 'wp_comments' change comment_author comment_author LONGTEXT CHARACTER SET utf8;


update `wp_posts` set `post_content` = replace(`post_content` ,'Ã¡','á');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ã©','é');
update `wp_posts` set `post_content` = replace(`post_content` ,'í©','é');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ã³','ó');
update `wp_posts` set `post_content` = replace(`post_content` ,'íº','ú');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ãº','ú');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ã±','ñ');
update `wp_posts` set `post_content` = replace(`post_content` ,'í‘','Ñ');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ã','í');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€“','–');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€”','–');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€™','\'');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€¦','...');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€“','-');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€œ','"');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€','"');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€˜','\'');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€¢','-');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€¡','c');
update `wp_posts` set `post_content` = replace(`post_content` ,'Â','');

update `wp_posts` set `post_title` = replace(`post_title` ,'Ã¡','á');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ã©','é');
update `wp_posts` set `post_title` = replace(`post_title` ,'í©','é');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ã³','ó');
update `wp_posts` set `post_title` = replace(`post_title` ,'íº','ú');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ãº','ú');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ã±','ñ');
update `wp_posts` set `post_title` = replace(`post_title` ,'í‘','Ñ');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ã','í');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€“','–');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€”','–');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€™','\'');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€¦','...');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€“','-');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€œ','"');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€','"');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€˜','\'');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€¢','-');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€¡','c');
update `wp_posts` set `post_title` = replace(`post_title` ,'Â','');

update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã¡','á');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã©','é');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'í©','é');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã³','ó');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'íº','ú');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ãº','ú');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã±','ñ');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'í‘','Ñ');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã','í');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€“','–');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€”','–');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€™','\'');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€¦','...');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€“','-');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€œ','"');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€','"');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€˜','\'');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€¢','-');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€¡','c');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Â','');