How to detect a SQL Server database’s read-only status using T-SQL?
The information is stored in sys.databases. SELECT name, is_read_only FROM sys.databases WHERE name=”MyDBNAme” GO –returns 1 in is_read_only when database is set to read-only mode.
tsql
How do I ‘subtract’ sql tables?
The set operation you are looking for is called MINUS, but in SQL Server the keyword is EXCEPT SELECT … // all documents EXCEPT SELECT … // active documents I believe that the EXCEPT set operation became available in SQL Server 2005.
Update and left outer join statements
UPDATE t SET t.Column1=100 FROM myTableA t LEFT JOIN myTableB t2 ON t2.ID=t.ID Replace myTableA with your table name and replace Column1 with your column name. After this simply LEFT JOIN to tableB. t in this case is just an alias for myTableA. t2 is an alias for your joined table, in my example that … Read more
What’s a good way to check if two datetimes are on the same calendar day in TSQL?
This is much more concise: where datediff(day, date1, date2) = 0
How to drop multiple databases in SQL Server
Why not just do this instead? USE master; Go SELECT ‘DROP DATABASE [‘+ name + ‘]’ FROM sys.databases WHERE name like ‘_database_name_%’; GO Capture the output of that resultset and then paste it into another query window. Then run that. Why write all this TSQL cursor code? “When you have a hammer, everything looks like … Read more
SQL returns less results when using UNION?
UNION will eliminate duplicate rows, whether they come between the two result sets or, as in your case, within just one result set. Try using UNION ALL instead.
SQL time difference between two dates result in hh:mm:ss
declare @StartDate datetime, @EndDate datetime select @StartDate=”10/01/2012 08:40:18.000″,@EndDate=”10/04/2012 09:52:48.000″ select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+’:’+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+’:’+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss] This query will helpful to you.
Add a column if it doesn’t exist to all tables?
You cannot use variables, like @tableName, in DDL. Besides, splinting the name into part and ignoring the schema can only result in bugs. You should just use the ”?” replacement in the SQL batch parameter and rely on the MSforeachtable replacement: EXEC sp_MSforeachtable ‘ if not exists (select * from sys.columns where object_id = object_id(”?”) … Read more
Why cast/convert from int returns an asterisk
For even more fun, try this one: DECLARE @i INT SET @i = 100 SELECT CAST(@i AS VARCHAR(2)) — result: ‘*’ go DECLARE @i INT SET @i = 100 SELECT CAST(@i AS NVARCHAR(2)) — result: Arithmetic overflow error 🙂 The answer to your query is: “Historical reasons” The datatypes INT and VARCHAR are older than … Read more
Access to Result sets from within Stored procedures Transact-SQL SQL Server
The short answer is: you can’t do it. From T-SQL there is no way to access multiple results of a nested stored procedure call, without changing the stored procedure as others have suggested. To be complete, if the procedure were returning a single result, you could insert it into a temp table or table variable … Read more