Category MSSQL

Create Filegroups based on time period

declare@databaseName varchar(100) =’mydb’,@datapath varchar(2000) = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\’,@period varchar (50) = ‘QUARTER’, — DAY, WEEK, MONTH, QUARTER, YEAR@firstFilegroupDate date =’2024-01-01′,@lastFilegroupDate date =’2027-02-01′,@tempDate date,@yearpart varchar (4),@quarterpart varchar (1),@monthpart varchar (3),@daypart varchar(2),@filegroupNameInit varchar(50)=’PART’,@filegroupName varchar(100),@fileName varchar(100),@sql nvarchar(3000)=”,@debug bit =0,@execution bit=0,@fgNo int=0,@i int=0;BEGINIF…

Create a Distributed Availability Group

A Distributed Availability Group (DAG) is an Availability Group that instead of nodes it consiss of two or more other Availability Groups. It is quite flexible and Availability Groups can be of different architecture, version even OS. For example it…

Sequence referenced by table

Find which table references a sequence qith the following statement: SELECT s.object_id AS sequence_object_id,s.name AS sequence_name,OBJECT_SCHEMA_NAME(o.parent_object_id) + ‘.’+ OBJECT_NAME(o.parent_object_id) AS table_name,r.*FROM sys.sequences sCROSS APPLY sys.dm_sql_referencing_entities(OBJECT_SCHEMA_NAME(s.object_id) + ‘.’ + s.name,’OBJECT’) rJOIN sys.objects oON o.object_id = r.referencing_id

Rows and space per partition

SELECT o.name as table_name, rv.value as partition_range,fg.name as file_groupName, p.partition_number,max(p.rows) as number_of_rows,sum(au.total_pages) *8 [Total MB],sum(au.used_pages)*8 [Used MB]FROM sys.partitions pINNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_idINNER JOIN sys.objects o ON p.object_id = o.object_idINNER JOIN sys.system_internals_allocation_units au…

Create SQL Loader Control file from SQL Server Tables

with cols as (select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name in (‘datetime’,’datetime2′,’date’) then ‘DATE’end oracle_typefrom sys.all_columns ac join sys.types t on t.system_type_id=ac.system_type_idjoin sys.tables st on st.object_id=ac.object_idjoin sys.schemas s on s.schema_id=st.schema_idwhere st.name!=’sysdiagrams’),grp as(select upper(table_name) table_name, col= upper(STUFF ((SELECT ‘,’ + column_name+’ ‘+isnull(oracle_type,”)…

Tables Columns and Data Types equivalence in Oracle

select s.name,st.name table_name,ac.name column_name,t.name column_type,ac.max_length,ac.precision,ac.scale,casewhen t.name=’sysname’ then ‘nvarchar2(128)’when t.name=’nvarchar’ then ‘nvarchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’varchar’ then ‘varchar2(‘+cast(ac.max_length as varchar)+’)’when t.name=’char’ then ‘char(‘+cast(ac.max_length as varchar)+’)’when t.name=’int’ then ‘number(10)’when t.name=’bigint’ then ‘number(19)’when t.name in (‘float’,’real’) then ‘number’when t.name=’smallint’ then ‘number(5)’when t.name=’tinyint’ then ‘number(3)’when…

Partition Automatic Handling

Automatic Partition SET ANSI_NULLS, QUOTED_IDENTIFIER ON; GO use <Database> Declare @nextRange date, @sql nvarchar(3000), @maxBoundary date, @tableList varchar(1000) =’Table1,Table2,Table3′, @tableName varchar(100), @partitionFunction varchar(100), @partitionScheme varchar(100), @PartitionedTables as cursor , @debug bit =0, @monthsPartitionFuture smallint =7,@filegroup = ‘Primary’ ; set @PartitionedTables…

Trending now

Tables size
Locks in PostgreSQL
Describe table in postgres
Get replica members