Structured Query Language Pronounced “S-Q-L” or “see-quill,” a language used to interrogate and process data in a relational database. Originally developed by IBM for its mainframes, all database systems designed for client/server environments support SQL. SQL commands can be used to interactively work with a database or can be embedded within a programming language to interface to a database. Programming extensions to SQL have turned it into a full-blown database programming language, and all major database management systems (DBMSs) support the language. Each month I intend on posting a collection of scripts that I have found beneficial for myself as well as something that you may be able to put to use.
Military Time Format
DECLARE @d DATETIME = GETDATE()
--ddHHmmMMMyy.toUpper (military format).
--That is two digit day, two digit hour, two digit minute, three letter month, two digit year, and month is uppercase.
SELECT RIGHT('00' + CAST(DATEPART(DAY,@d) AS VARCHAR(2)),2) +
RIGHT('00' + CAST(DATEPART(HOUR,@d) AS VARCHAR(2)),2) +
RIGHT('00' + CAST(DATEPART(MINUTE,@d) AS VARCHAR(2)),2) +
UPPER(LEFT(DATENAME(MONTH,@d),3)) + RIGHT(CAST(DATEPART(YEAR,@d) AS VARCHAR(4)),2)
Retrieve all Primary and Foreign Keys
SELECT t.table_schema AS PrimarySchemaName ,
t.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(tc2.constraint_schema,'N/A') AS ForeignSchemaName,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.TABLES t ON tc.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
List All Tables That Contain No Triggers
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(SELECT OBJECT_NAME(o.parent_obj) AS TableName
FROM sysobjects o
WHERE OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
AND TABLE_TYPE ='BASE TABLE'
ORDER BY t.TABLE_NAME
Calculate An Age Based Upon YYYYMMDD
DECLARE @Birthday DATETIME, @DateToCheck DATETIME
SELECT @Birthday = '19680401', @DateToCheck = CURRENT_TIMESTAMP
SELECT DATEDIFF(YEAR, @Birthday, @DateToCheck) -
CASE WHEN DATEPART(mm,@Birthday) > DATEPART(mm,@DateToCheck)
OR (DATEPART(mm,@Birthday) = DATEPART(mm,@DateToCheck)
AND DATEPART(dd,@Birthday) > DATEPART(dd,@DateToCheck))
THEN 1 ELSE 0 END
Formatting Dates
DECLARE @d DATETIME
SELECT @d = GETDATE()
SELECT @d AS OriginalDate,
CONVERT(VARCHAR,@d,100) AS ConvertedDate,
100 AS FormatValue,
'mon dd yyyy hh:miAM (or PM)' AS OutputFormat
UNION all
SELECT @d,CONVERT(VARCHAR,@d,101),101,'mm/dd/yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,102),102,'yyyy.mm.dd'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,103),103,'dd/mm/yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,104),104,'dd.mm.yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,105),105,'dd-mm-yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,106),106,'dd mon yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,107),107,'Mon dd, yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,108),108,'hh:mm:ss'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,110),110,'mm-dd-yyyy'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,111),111,'yyyy/mm/dd'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,112),112,'yyyymmdd'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,114),114,'hh:mi:ss:mmm(24h)'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
UNION all
SELECT @d,CONVERT(VARCHAR,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
Enabling xp_cmdshell in 2005/2008
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
Selecting From System Tables in 2005/2008
SELECT * FROM sys.objects -- new objects table
SELECT * FROM sys.sysobjects -- SQL Server 2000 style objects table
SELECT * FROM sys.all_columns
SELECT * FROM sys.all_objects -- system objects included also with user-defined
SELECT * FROM sys.all_parameters -- stored procedure and function parameters
SELECT * FROM sys.all_sql_modules -- stored procedures, functions, triggers, app views
SELECT * FROM sys.all_views
Converting A String To DatTime
DECLARE
@DateTimeValue varchar(30),
@DateValue char(8),
@TimeValue char(6)
SELECT
@DateValue = '20081023',
@TimeValue = '211920'
SELECT @DateTimeValue =
convert(varchar, convert(datetime, @DateValue), 110)
+ ' ' + substring(@TimeValue, 1, 2)
+ ':' + substring(@TimeValue, 3, 2)
+ ':' + substring(@TimeValue, 5, 2)
SELECT DateInput = @DateValue,
TimeInput = @TimeValue,
DateTimeOutput = @DateTimeValue,
DateTimeFormat = convert(datetime, @DateTimeValue)
List All Tables
SELECT SchemaName = SCHEMA_NAME(schema_id),
TableName = name
FROM sys.objects
WHERE TYPE = 'U'
ORDER BY SchemaName,
TableName
GO
List All Triggers
SELECT SchemaName = SCHEMA_NAME(schema_id),
TableName = name
FROM sys.objects
WHERE TYPE = 'TR'
ORDER BY SchemaName,
TableName
GO
List All Views
SELECT SchemaName = SCHEMA_NAME(schema_id),
TableName = name
FROM sys.objects
WHERE TYPE = 'V'
ORDER BY SchemaName,
TableName
GO
List All Stored Procedures
SELECT SchemaName = SCHEMA_NAME(schema_id),
TableName = name
FROM sys.objects
WHERE TYPE = 'P'
ORDER BY SchemaName,
TableName
GO
Current System Date Conversions
SELECT Now=GETDATE() -- 2016-10-23 18:59:09.483
SELECT CONVERT(datetime, getdate()) -- 2016-10-23 18:59:09.483
SELECT CONVERT(datetime2, getdate()) -- 2016-10-23 18:59:09.4830000
SELECT CONVERT(smalldatetime, getdate()) -- 2016-10-23 18:59:00
SELECT CONVERT(date, getdate()) -- 2016-10-23
SELECT CONVERT(datetime, CURRENT_TIMESTAMP) -- 2016-10-23 18:59:09.483
-- SQL Server current system date functions
SELECT SYSDATETIME() -- 2016-10-23 19:04:34.28125007
,SYSDATETIMEOFFSET() -- 2016-10-23 19:04:34.2812500 -04:00
,SYSUTCDATETIME() -- 2016-10-23 23:04:34.2812500
,CURRENT_TIMESTAMP -- 2016-10-23 19:04:34.280
,GETDATE() -- 2016-10-23 19:04:34.280
,GETUTCDATE(); -- 2016-10-23 23:04:34.280
-- SQL Server current system date functions with conversions
SELECT CONVERT (datetime, SYSDATETIME()) -- 2016-10-23 19:02:19.547
,CONVERT (datetime, SYSDATETIMEOFFSET()) -- 2016-10-23 19:02:19.547
,CONVERT (datetime, SYSUTCDATETIME()) -- 2016-10-23 23:02:19.547
,CONVERT (datetime, CURRENT_TIMESTAMP) -- 2016-10-23 19:02:19.543
,CONVERT (datetime, GETDATE()) -- 2016-10-23 19:02:19.543
,CONVERT (datetime, GETUTCDATE()); -- 2016-10-23 23:02:19.543
Random Password Generation
DECLARE
@complex tinyint
, @minlen tinyint
, @maxlen tinyint
SET @minlen = 8 --min length of password
SET @maxlen = 12 --max length of password
SET @complex = 4
-- 1 all lowercase
-- 2 include upper case
-- 3 include number
-- 4 include punctuation
DECLARE
@password varchar(12)
, @len tinyint
, @type tinyint
, @type2 tinyint
SET @len = 0
SET @password = ''
WHILE @len NOT BETWEEN @minlen and @maxlen
BEGIN
SET @len = ROUND(1 + (RAND(CHECKSUM(NEWID())) * @maxlen), 0) + 1
END
WHILE @len > 0
BEGIN
DECLARE @newchar CHAR(1)
SET @type = ROUND(1 + (RAND(CHECKSUM(NEWID())) * (@complex - 1)), 0)
IF @type = 1
SET @newchar = CHAR(ROUND(97 + (RAND(CHECKSUM(NEWID())) * 25), 0))
IF @type = 2
SET @newchar = CHAR(ROUND(65 + (RAND(CHECKSUM(NEWID())) * 25), 0))
IF @type = 3
SET @newchar = CHAR(ROUND(48 + (RAND(CHECKSUM(NEWID())) * 9), 0))
IF @type = 4
BEGIN
SET @type2 = ROUND(1 + (RAND(CHECKSUM(NEWID())) * 3), 0)
IF @type2 = 1
SET @newchar = CHAR(ROUND(33 + (RAND(CHECKSUM(NEWID())) * 14), 0))
IF @type2 = 2
SET @newchar = CHAR(ROUND(58 + (RAND(CHECKSUM(NEWID())) * 6), 0))
IF @type2 = 3
SET @newchar = CHAR(ROUND(91 + (RAND(CHECKSUM(NEWID())) * 5), 0))
IF @type2 = 4
SET @newchar = CHAR(ROUND(123 + (RAND(CHECKSUM(NEWID())) * 3), 0))
END
-- remove invalid characters as well as characters easily confused with others
IF @newchar NOT IN ('b', 'l', 'o', 's', 'I', 'O', 'S', '0', '1', '!', '''', '.', ',', '/', '`', '', '|')
BEGIN
SET @password = @password + @newchar
SET @len = @len - 1
END
END
SELECT @password as Password
Copy Data From One Table Into Another
INSERT INTO [TABLE2]
SELECT *
FROM [TABLE1]
Basic Cursor
declare @email nvarchar(255)
declare CustList cursor for
SELECT email from STE_EMAILS
OPEN CustList
FETCH NEXT FROM CustList
INTO @email
WHILE @@FETCH_STATUS = 0
BEGIN
if (SELECT count(custid) from customer where (current_customer=1) and (notes1 is not null) and (notes1 like '%' + @email + '%')) > 0
SELECT custid from customer where (current_customer=1) and (notes1 is not null) and (notes1 like '%' + @email + '%')
print '%' + @email + '%'
FETCH NEXT FROM CustList INTO @email
END
CLOSE CustList
DEALLOCATE CustList
Do you have a script that you would like to share? Feel free to contact me and I will add it to the list.
As you may have noticed last Friday evening my website was extremely slow and when it loaded it resulted in error after error. As soon as I saw the problem I begun to investigate the root cause in order to determine the best approach to resolve the issue. To my surprise 
Recent Comments