SELECT COL_TO_CHECK, COUNT(COL_TO_CHECK) FROM TABLE_NAME GROUP BY COL_TO_CHECK HAVING COUNT(COL_TO_CHECK) > 1 If there is more than 1 column with that value it will show up like: COL_TO_CHECK COUNT(COL_TO_CHECK) ————————————————————- Value 3 Value2 2 Value99 4
Returns a report that has information about user permissions for an object, or statement permissions, in the current database. To find stored procedures that “username” can access: exec sp_helprotect @username = ‘username’ To see all objects in database and who has access: exec sp_helprotect For complete capabilities see http://msdn.microsoft.com/en-us/library/ms190310.aspx
Within a Stored Procedure, you may want the results of a particular query emailed to you, but only if there is a result. In other words, I don’t want to be notified when the result set size is 0. When you put a query into the “@query” of the stored...
Source of info: http://www.mattbutton.com/2011/06/01/sql-profiler-templates-missing/ Upon trying to create a trace template in SQL Server Profiler, I was able to name the trace but there were no events to choose from in the “Events Selection” tab, even when the “Show All Events” box was checked. If I clicked the “Column Filters” button...
This is the basic query text to use whenever you need to loop through a result set in SQL Server using TSQL. In this example we are getting every database name and printing it out. /* Declare variable that will hold a single result */ DECLARE @DBName varchar(50) /* Declare...
Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code: No Transactions You can see the third row having a value of “5”, showing that it was updated CREATE TABLE #T( ID INT IDENTITY(1,1) ,COL1 VARCHAR(100) ,COL2 VARCHAR(100) );...
—- Allow advanced options to be changed. –EXEC sp_configure ‘show advanced options’, 1 –GO —- Update the cuently configured value for advanced options. –RECONFIGURE –GO —- Enable xp_cmdshell –EXEC sp_configure ‘xp_cmdshell’, 1 –GO —- Update the cuently configured value for xp_cmdshell –RECONFIGURE –GO —- Disallow further advanced options to be...
$assemblylist = “Microsoft.SqlServer.Management.Common”, “Microsoft.SqlServer.Smo”, “Microsoft.SqlServer.Dmf “, “Microsoft.SqlServer.Instapi “, “Microsoft.SqlServer.SqlWmiManagement “, “Microsoft.SqlServer.ConnectionInfo “, “Microsoft.SqlServer.SmoExtended “, “Microsoft.SqlServer.SqlTDiagM “, “Microsoft.SqlServer.SString “, “Microsoft.SqlServer.Management.RegisteredServers “, “Microsoft.SqlServer.Management.Sdk.Sfc “, “Microsoft.SqlServer.SqlEnum “, “Microsoft.SqlServer.RegSvrEnum “, “Microsoft.SqlServer.WmiEnum “, “Microsoft.SqlServer.ServiceBrokerEnum “, “Microsoft.SqlServer.ConnectionInfoExtended “, “Microsoft.SqlServer.Management.Collector “, “Microsoft.SqlServer.Management.CollectorEnum”, “Microsoft.SqlServer.Management.Dac”, “Microsoft.SqlServer.Management.DacEnum”, “Microsoft.SqlServer.Management.Utility” foreach ($asm in $assemblylist) { $asm = [Reflection.Assembly]::LoadWithPartialName($asm) } $computers...
Do transactions apply in dynamic SQL, in SQL Server? In a word, yes. For proof, run the following chunks of code: