Common SQL for working with databases (SQL SERVER)

Common SQL
--View the number of table records
SELECT a.id, B.[name], a.rowcnt from sysindexes A, sys.tables b WHERE a.id = b.[object_id] and A.indid <=1 ORDER by b.[ Name

--View File groups
SELECT name, data_space_id file group ID, size/128 [File Size (trillion)],
Fileproperty (name, ' spaceused ')/128 [used space (trillion)],
Size/128-fileproperty (name, ' spaceused ')/128 [unused space (trillion)],
Fileproperty (name, ' spaceused ') *100.0/size [utilization (%)],
max_size/128 [maximum value (trillion)],
Case Is_percent_growth while 0 then cast (growth/128 as nvarchar) + ' mega ' else cast (growth as nvarchar) + '% ' end growth value,
Physical_name Physical Path
From Sys.database_files a ORDER by A.[name]

--Create file groups

ALTER DATABASE computingdb_en ADD FILEGROUP [fg_dailyusers_2017]; ALTER DATABASE computingdb_en ADD FILE (name=n ' fg_dailyusers_2017 ', filename=n ' ... ndf ', size=10mb,maxsize= UNLIMITED,FILEGROWTH=10MB) to FILEGROUP [fg_dailyusers_2017];

--View database size
SELECT sum (size/128) [File (m)] from sys.database_files

Partitioning operations
--Number of partition records
Select Partition_number,rows from sys.partitions where object_id=object_id (' xxxx ') and index_id in (0,1) Order by Partitio N_number;

--Toggle Partition
ALTER TABLE Pczsfunctionlog switch partition Tmp_pczsfunctionlog partition 19;

--Append partition, can only one one plus
ALTER PARTITION SCHEME [date_ps] NEXT used [date_10];
ALTER PARTITION FUNCTION date_pf () SPLIT RANGE (20141001);

--Merge partitions
ALTER PARTITION FUNCTION date_pf () MERGE RANGE (20141001);

Filegroup Action
-Generate a filegroup creation script
Select name, ' ALTER DATABASE ' +db_name () + ' ADD FILEGROUP [' +name+ ']; ALTER DATABASE ' +db_name () + ' ADD FILE (name=n ' +name+ ', filename=n ' +physical_name+ ', size=10mb,maxsize= UNLIMITED,FILEGROWTH=10MB) to FILEGROUP [' +name+ ']; '
from Sys.database_files a
where data_space_id>1
Order by A.name

--delete filegroup
DROP table dbo. mobileuselonglog_201305;
Drop partition Scheme day_ps_201305;
DBCC SHRINKFILE (' data_201305 ', emptyfile);
ALTER DATABASE softuselogdb remove file data_201305;
ALTER DATABASE SOFTUSELOGDB remove filegroup data_201305;

--add filegroup
ALTER DATABASE GHW ADD filegroup [data_201207];
ALTER DATABASE GHW Add file (name= ' data_201207 ',
filename= ' e:\data\ghw\DATA_201207.ndf ',
SIZE=10MB,
maxsize=unlimited,
FILEGROWTH=10MB)
to filegroup data_201207

MySQL Related
--View the number of records
Select Table_name,table_rows from Information_schema.tables where table_schema= ' Stdb '

--View the number of partitions
Select Partition_ordinal_position part,table_rows
From Information_schema.partitions
where Table_schema = Schema () and table_name= ' func_201506 ';

--Table structure replication
CREATE table if not exists functionlog_201508 like func_201506

--Batch Update
Update a INNER JOIN B on a.id=b.id set A.name=b.name

--Unique insertion
Insert into a Select the name from dual where NOT exists (select 1 from a where id=?id);

Select last_insert_id ();

--First update and then insert
Update Cfg_pczsvid set name=?name where Vid=?vid;
Insert into Cfg_pczsvid (vid,name)
Select? Vid,?name from dual where row_count () = 0;

--View process
Show Processlist

--Kill query, reference
KILL [CONNECTION | QUERY] thread_id

--Displays basic information about all stored procedures in the database, including the owning database, stored procedure name, creation time, etc.
Show procedure Status

--Displays detailed information about a stored procedure
Show CREATE PROCEDURE Sp_name

Common SQL (SQL SERVER) for working with databases