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.
The facial recognition feature is technology you may take for granted when you upload images on Facebook.
Recent Comments