SQL Function to Generate Date Table with Count

-- select * from [dbo].[GenDates2]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates2]( 'y', getdate()-1, getdate()-16384 );
create or alter function [dbo].[GenDates2](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3), [Count] int )
as
begin;
	set @Increment = lower( @Increment );

	with cte( [Date], [Count] ) AS (
		select @Start [Date], 0 [Count]
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end,
		case
			when @Increment = 'd' THEN datediff( day, @Start, dateadd(day, 1, [Date]))
			when @Increment = 'w' THEN datediff(week, @Start, dateadd(week, 1, [Date]))
			when @Increment = 'm' THEN datediff(month, @Start, dateadd(month, 1, [Date]))
			when @Increment = 'y' THEN datediff(year, @Start, dateadd(year, 1, [Date]))
		end [Count]
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date], [Count] )
	select [Date], [Count]
	from [cte]
	option (maxrecursion 32767);

	return;
end;

SQL Function to Generate Date Table

Update: Please try the Improved version of this script.

-- select * from [dbo].[GenDates]( 'd', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'w', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'm', getdate()-16383, getdate()+16384 );
-- select * from [dbo].[GenDates]( 'y', getdate()-16383, getdate()+16384 );
create or alter function [dbo].[GenDates](
	@Increment char(1),
	@Start datetime2(3),
	@End datetime2(3)
)
returns	@Range table( [Date] datetime2(3) )
as
begin;
	set @Increment = lower( @Increment );

	with [cte]( [Date] ) AS (
		select @Start
		union all
		select
			case
				when @Increment = 'd' THEN dateadd(day, 1, [Date])
				when @Increment = 'w' THEN dateadd(week, 1, [Date])
				when @Increment = 'm' THEN dateadd(month, 1, [Date])
				when @Increment = 'y' THEN dateadd(year, 1, [Date])
			end
		from [cte]
		where [Date] <= 
			case
				when @Increment = 'd' THEN dateadd(day, -1, @End)
				when @Increment = 'w' THEN dateadd(week, -1, @End)
				when @Increment = 'm' THEN dateadd(month, -1, @End)
				when @Increment = 'y' THEN dateadd(year, -1, @End)
			end
	)
	insert into @Range( [Date] )
	select [Date]
	from [cte]
	option (maxrecursion 32767);

	return;
end;

Enable Windows Firewall for SQL Server

Run these commands on the Windows Server to open the ports for listening on a named instance of SQL Server.
Note: Remember to enable TCP in the SQL Server Configuration Manager.

netsh firewall set portopening protocol=TCP port=1433 name=SQLServerTCP mode=ENABLE scope=SUBNET profile=CURRENT

netsh firewall set portopening protocol=UDP port=1434 name=SQLServerUDP mode=ENABLE scope=SUBNET profile=CURRENT

For more help, refer to SQL Server & Windows Firewall.

Example: How to MOVE rows from first table to a second table in a single transaction in SQL Server.

-- NOTE: This is an EXAMPLE to show it is possible
-- to achieve moving rows using an implicit transaction
-- from one table into another table.

-- Set up example tables, Foo & Bar.
drop table if exists [dbo].[Bar];
drop table if exists [dbo].[Foo];

create table [dbo].[Bar](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] null index ix_mycol
)

create table [dbo].[Foo](
	[ID] [int] identity(1,1) not null primary key clustered,
	[MyKey] [int] null index ix_mykey,
	[MyColumn] [int] index ix_mycol,
)
GO

-- Create some dummy data
insert into [dbo].[Foo]( [MyKey], [MyColumn] )
values (1, 11), (2, 22), (3, 33), (4, 44);
GO 10240

-- Verify the rows before.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

-- Move rows from table Foo into table Bar using two styles.

-- Method 1: "Direct" move
delete
from [dbo].[Foo]
output [deleted].[MyKey], [deleted].[MyColumn]
into [dbo].[Bar]( [MyKey], [MyColumn] )
where [ID] % 3 = 1;


-- Method 2: "Subquery" move
insert [dbo].[Bar]( [MyKey], [MyColumn] )
select [MyKey], [MyColumn]
from (
	delete
	from [dbo].[Foo]
	output deleted.*
	where [ID] % 3 = 2
) [MovedRows]
where [MovedRows].[MyKey] = 2;
-- Note: The last where is not needed, just showing it is possible to re-filter incoming [from the deleted output] rows.

-- Verify the counts after.
-- Note: Any deleted rows filtered out via Method 2 are now gone.
select count(*) from [dbo].[Foo];
select count(*) from [dbo].[Bar];

Send a text message from SQL Server 2019

NOTE: You must have SQL Server database mail enabled for this stored procedure to work.

create or alter proc [dbo].[SendTextMessage](
	@number sysname
	,@message nvarchar(max)
	,@subject sysname = null
	,@provider nvarchar(20) = N'Verizon'
)
as
begin;
	set nocount on;

	set @number = trim(@number);
	if @number is null or len(@number)<7 begin;
		set @message = N'@number is too short';
		throw 51000, @message, 1;
	end;

	set @message = trim(@message);
	if @message is null or len(@message)<2 begin;
		set @message = N'@message is too short';
		throw 51000, @message, 1;
	end;

	set @subject = trim(@subject);
	if @subject is null set @subject = @@SERVERNAME + N' Notification';

	declare @to nvarchar(255) = @number + N'@'+
		case @provider
			when N'Verizon' then N'vtext.com'
			when N'VZ' then N'vtext.com'
			when N'V' then N'vtext.com'
			when N'ATT' then N'txt.att.net'
			when N'SPRINT' then N'messaging.sprintpcs.com'
			when N'TMOBILE' then N'tmomail.net'
	end;

	if nullif(@provider,N'@') is null begin;
		throw 51000, 'Unknown provider. Message not sent.', 1;
	end;

	begin try;
		declare @mailitem_id int;
		exec msdb.dbo.sp_send_dbmail @recipients = @to, @subject = @subject, @body = @message, @mailitem_id=@mailitem_id output;
	end try
	begin catch;
		throw 51000, 'Unknown error calling msdb.dbo.sp_send_dbmail.', 1;
	end catch;
end;

SQL Server Table Smells – Simple-Talk

Here is the corrected version of Phil’s table smell:
Note: This needs to be run per database.

create proc dbo.sp_CodeSmells
as
;WITH TableSmells (TableName, Problem, Object_ID )AS
(
SELECT object_schema_name(Object_ID)+'.'+object_name(Object_ID), Problem,Object_ID FROM
(
SELECT object_id, 'wide (more than 15 columns)'
FROM sys.tables /* see whether the table has more than 15 columns */
WHERE max_column_id_used>15
UNION ALL
SELECT DISTINCT sys.tables.object_id, 'heap'
FROM sys.indexes/* see whether the table is a heap */
INNER JOIN sys.tables ON sys.tables.object_id=sys.indexes.object_id
WHERE sys.indexes.type=0
UNION ALL
SELECT sys.tables.object_id, 'No primary key'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'TableHasPrimaryKey') = 0
UNION ALL
SELECT sys.tables.object_id, 'No index at all'
FROM sys.tables /* see whether the table has any index */
WHERE objectproperty(object_id,'TableHasIndex') = 0
UNION ALL
SELECT sys.tables.object_id, 'No candidate key'
FROM sys.tables/* if no unique constraint then it isn't relational */
WHERE objectproperty(object_id,'
TableHasUniqueCnst') = 0
AND objectproperty(object_id,'
TableHasPrimaryKey') = 0
UNION ALL
SELECT DISTINCT object_id, '
disabled Index(es)'
FROM sys.indexes /* don'
t leave these lying around */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled constraint(s)'
FROM sys.check_constraints /* hmm. i wonder why */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted constraint(s)'
FROM sys.check_constraints /* ETL gone bad? */
WHERE is_not_trusted=1
UNION ALL
SELECT DISTINCT parent_object_id, 'disabled FK'
FROM sys.foreign_keys /* build script gone bad? */
WHERE is_disabled=1
UNION ALL
SELECT DISTINCT parent_object_id, 'untrusted FK'
FROM sys.foreign_keys /* Why do you have untrusted FKs?
Constraint was enabled without checking existing rows;
therefore, the constraint may not hold for all rows. */
WHERE is_not_trusted=1
UNION ALL
/*
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables
LEFT OUTER join
(SELECT referenced_object_id AS table_ID
FROM sys.foreign_keys
UNION ALL
SELECT parent_object_id
FROM sys.foreign_keys
)referenced(table_ID)
ON referenced.table_ID=sys.Tables.object_ID
WHERE referenced.table_id IS null*/
SELECT sys.tables.object_id, 'unrelated to any other table'
FROM sys.tables /* found a simpler way! */
WHERE objectpropertyex(object_id,'TableHasForeignKey')=0
AND objectpropertyex(object_id,'TableHasForeignRef')=0
UNION ALL
SELECT DISTINCT object_id, 'unintelligible column names'
FROM sys.columns /* column names with no letters in them */
WHERE name COLLATE Latin1_general_CI_AI
NOT LIKE '%[A-Z]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT object_id, 'non-compliant column names'
FROM sys.columns /* column names that need delimiters*/
WHERE name COLLATE Latin1_general_CI_AI
LIKE '%[^_@$#A-Z0-9]%' COLLATE Latin1_general_CI_AI
UNION ALL
SELECT DISTINCT parent_id, 'has a disabled trigger'
FROM sys.triggers
WHERE is_disabled=1 AND parent_id>0
UNION ALL
SELECT sys.tables.object_id, 'can''t be indexed'
FROM sys.tables/* see whether the table has a primary key */
WHERE objectproperty(object_id,'IsIndexable') = 0
)f(Object_ID,Problem)
)
SELECT TableName,
CASE WHEN count(*)>1 THEN /*only do correlated subquery when necessary*/
stuff(( SELECT ', '+Problem
FROM TableSmells t2
WHERE t1.TableName = t2.TableName
ORDER BY Problem
FOR XML PATH(''), TYPE).value(N'(./text())[1]',N'varchar(8000)'),1,2,'')
ELSE max(Problem) END
FROM TableSmells t1 WHERE OBJECTPROPERTYEX(t1.Object_ID, 'IsTable')=1
GROUP BY TableName;

Frustration with Bad Design

“This is not how you handle things. When you find security issues, and they don’t get fixed, it’s one thing to attempt to prove a point with a PEN test. It’s quite another to publicly expose information. You might find yourself in trouble, and you should be in trouble. This is a violation of the professional responsibility you undertake when working for someone.

This is the type of frustration that occurs in many IT workers. I’ve seen more than a few people working in technology that are sure they know how to properly configure and manage an application. They know how to set up security, and they become upset with a company that doesn’t do a good job of running internal systems. They know that the architecture chosen for their application will fail when a load is applied.

There are some smart people in IT, but sometimes they think they’re smarter than they are. Bad design, bad decisions, mistakes, even poor security practices will occur. However it’s usually not your company, and it’s not your place to prove that there is a flaw in a system. It’s especially true that it’s not your place to prove things without having been given permission to do so. Proving a point on your own is something children do, not professionals.

When you find problems in your organization, it is your responsibility to report them. I hope you think it’s your job as a professional to do the best job you can, following the best practices as we know them. It’s also your decision to choose to leave a job if you can’t go along with, or abide by, the decisions made by your management.

If your company has chosen poorly in their technology decisions, I understand your frustration. I’ve often shared it, but I’d advise you to do what I’ve done. State your objections and either support the chosen path or find another job.”

-written by Steve Jones from SQLServerCentral.com