Convert varchar to datetime

,CONVERT(DATETIME, MIN(CONVERT(VARCHAR(11), [ivst_from], 120) + CASE
WHEN LEN(CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))))) < 4 THEN '00:00:00'WHEN LEN(CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))))) = 4 THEN '0'+ CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))) + ':00')WHEN LEN(CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))))) = 5 THEN CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))) + ':00')WHEN LEN(CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))))) = 6 THEN CONVERT(VARCHAR(8), LTRIM(RTRIM('0'+REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))) + ':00')WHEN LEN(CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))))) = 7 THEN '0'+ CONVERT(VARCHAR(8), LTRIM(RTRIM(REPLACE(REPLACE([ivst_ftime], 'AM', ''), 'PM', ''))) + ':00')ELSE
CASE WHEN CAST(SUBSTRING(CONVERT(VARCHAR(8), [ivst_ftime]),1,2) AS INT) > 23 THEN '00:00:00' ELSE CONVERT(VARCHAR(8), [ivst_ftime]) END END)) AS [EffDate]


set @now = getDate();
set @month = right(N'0' + convert(nvarchar(2), month(@now)), 2);
set @day = right(N'0' + convert(nvarchar(2), day(@now)), 2);

TSQL datetime format

datestring                           style example
------------------------------ ----------- ---------------------------------------------
08/22/12                                 1 SELECT CONVERT(nvarchar(100),getdate(),1)
12.08.22                                 2 SELECT CONVERT(nvarchar(100),getdate(),2)
22/08/12                                 3 SELECT CONVERT(nvarchar(100),getdate(),3)
22.08.12                                 4 SELECT CONVERT(nvarchar(100),getdate(),4)
22-08-12                                 5 SELECT CONVERT(nvarchar(100),getdate(),5)
22 Aug 12                                6 SELECT CONVERT(nvarchar(100),getdate(),6)
Aug 22, 12                               7 SELECT CONVERT(nvarchar(100),getdate(),7)
14:23:00                                 8 SELECT CONVERT(nvarchar(100),getdate(),8)
Aug 22 2012  2:23:00:357PM               9 SELECT CONVERT(nvarchar(100),getdate(),9)
08-22-12                                10 SELECT CONVERT(nvarchar(100),getdate(),10)
12/08/22                                11 SELECT CONVERT(nvarchar(100),getdate(),11)
120822                                  12 SELECT CONVERT(nvarchar(100),getdate(),12)
22 Aug 2012 14:23:00:357                13 SELECT CONVERT(nvarchar(100),getdate(),13)
14:23:00:360                            14 SELECT CONVERT(nvarchar(100),getdate(),14)
2012-08-22 14:23:00                     20 SELECT CONVERT(nvarchar(100),getdate(),20)
2012-08-22 14:23:00.363                 21 SELECT CONVERT(nvarchar(100),getdate(),21)
08/22/12  2:23:00 PM                    22 SELECT CONVERT(nvarchar(100),getdate(),22)
2012-08-22                              23 SELECT CONVERT(nvarchar(100),getdate(),23)
14:23:00                                24 SELECT CONVERT(nvarchar(100),getdate(),24)
2012-08-22 14:23:00.367                 25 SELECT CONVERT(nvarchar(100),getdate(),25)
Aug 22 2012  2:23PM                    100 SELECT CONVERT(nvarchar(100),getdate(),100)
08/22/2012                             101 SELECT CONVERT(nvarchar(100),getdate(),101)
2012.08.22                             102 SELECT CONVERT(nvarchar(100),getdate(),102)
22/08/2012                             103 SELECT CONVERT(nvarchar(100),getdate(),103)
22.08.2012                             104 SELECT CONVERT(nvarchar(100),getdate(),104)
22-08-2012                             105 SELECT CONVERT(nvarchar(100),getdate(),105)
22 Aug 2012                            106 SELECT CONVERT(nvarchar(100),getdate(),106)
Aug 22, 2012                           107 SELECT CONVERT(nvarchar(100),getdate(),107)
14:23:00                               108 SELECT CONVERT(nvarchar(100),getdate(),108)
Aug 22 2012  2:23:00:433PM             109 SELECT CONVERT(nvarchar(100),getdate(),109)
08-22-2012                             110 SELECT CONVERT(nvarchar(100),getdate(),110)
2012/08/22                             111 SELECT CONVERT(nvarchar(100),getdate(),111)
20120822                               112 SELECT CONVERT(nvarchar(100),getdate(),112)
22 Aug 2012 14:23:00:437               113 SELECT CONVERT(nvarchar(100),getdate(),113)
14:23:00:437                           114 SELECT CONVERT(nvarchar(100),getdate(),114)
2012-08-22 14:23:00                    120 SELECT CONVERT(nvarchar(100),getdate(),120)
2012-08-22 14:23:00.440                121 SELECT CONVERT(nvarchar(100),getdate(),121)
2012-08-22T14:23:00.443                126 SELECT CONVERT(nvarchar(100),getdate(),126)
2012-08-22T14:23:00.443                127 SELECT CONVERT(nvarchar(100),getdate(),127)
 5 شوال 1433  2:23:00:447PM            130 SELECT CONVERT(nvarchar(100),getdate(),130)
 5/10/1433  2:23:00:447PM              131 SELECT CONVERT(nvarchar(100),getdate(),131)

Change schema owner, delete user

SELECT s.name
FROM sys.schemas s
WHEREs.principal_id = USER_ID('user_name')

ALTER AUTHORIZATION ON SCHEMA::[found_object_above] TO [dbo]

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user_name')
DROP USER [user_name]

xp_cmdshell Server Configuration Option

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options' , 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell' , 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Constraints

Add a check constraint to make it read only:

ALTER TABLE mytable WITH NOCHECK ADD CONSTRAINT chk_read_only CHECK( 1 = 0 )

What about a delete?

ALTER TRIGGER mytable_ReadOnly ON mytable

INSTEAD OF DELETE

AS

BEGIN

    RAISERROR( 'Table set to read only.', 16, 1 )

    ROLLBACK TRAN

END

Drop all contraints: 

DECLARE @TableName NVARCHAR(MAX)

DECLARE @ConstraintName NVARCHAR(MAX)
DECLARE Constraints CURSOR FOR
     SELECT OBJECT_NAME(parent_object_id), name
     FROM sys.object
     WHERE (is_ms_shipped = 0 AND type_desc LIKE '%_CONSTRAINT' AND OBJECT_NAME(parent_object_id) NOT IN ('sysdiagrams', 'Tablename')
     OPEN Constraints
     FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
     WHILE @@FETCH_STATUS = 0
     BEGIN
         EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ConstraintName + ']')
         FETCH NEXT FROM Constraints INTO @TableName, @ConstraintName
         PRINT 'DROP CONSTRAINT [' + @ConstraintName + ']'
     END

CLOSE Constraints
DEALLOCATE Constraints

-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL

-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

-- Find FK constraint
SELECT
 f.name AS ForeignKey,
 OBJECT_NAME(f.parent_object_id) AS TableName,
 COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
 COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE (SCHEMA_NAME(f.SCHEMA_ID)) = N'dbo' AND (OBJECT_NAME (f.referenced_object_id) = @tname)


-- Drop and Add Table Constraints
DECLARE @FK_Keys TABLE
(
 [Action] nvarchar(4)
 ,[SQLStr] nvarchar(1000)
);
DECLARE @sqlSTR nvarchar(1000), @tname nvarchar(50)=N'Facility';

INSERT INTO @FK_Keys
 SELECT
 'DROP' [ACTION]
 ,N'ALTER TABLE [dbo].' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + N' DROP CONSTRAINT ' + fk.name [SQLStr]
  FROM sys.foreign_keys AS fk
   INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id
   INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
  WHERE (SCHEMA_NAME(fk.SCHEMA_ID)) = N'dbo' AND (OBJECT_NAME (fk.referenced_object_id) = @tname);

INSERT INTO @FK_Keys
 SELECT
 'ADD' [ACTION]
 ,N'ALTER TABLE [dbo].' + QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + N' WITH CHECK ADD CONSTRAINT ' + fk.name + ' FOREIGN KEY (' + COL_NAME(fc.parent_object_id,fc.parent_column_id) + ') REFERENCES ' + OBJECT_NAME (fk.referenced_object_id) + ' (' + COL_NAME(fc.referenced_object_id,fc.referenced_column_id) + ')' [SQLStr]
  FROM sys.foreign_keys AS fk
   INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id
   INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
  WHERE (SCHEMA_NAME(fk.SCHEMA_ID)) = N'dbo' AND (OBJECT_NAME (fk.referenced_object_id) = @tname);

--declare @var_xml xml;
--select @var_xml = (SELECT * from @FK_Keys for xml auto);

WHILE EXISTS(SELECT fk.Name
  FROM sys.foreign_keys AS fk
   INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id
   INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
  WHERE (SCHEMA_NAME(fk.SCHEMA_ID)) = N'dbo' AND (OBJECT_NAME (fk.referenced_object_id) = @tname))
BEGIN
 SELECT @sqlSTR = (SELECT [SQLStr] FROM @FK_Keys WHERE [ACTION] = 'DROP');
 EXEC sp_executesql @sqlSTR;
END 

Dependencies

How to use sp_MSdependencies for Sql 2008 datebase objects.