Category MSSQL

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…

Find all indexes on a table

It is useful to be able to see all indexes on a table. There is the shipped stored procedure sp_helpindex but the following query can reveal some more inforamtion such as the Included and Filter columns.