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) );...
Source: http://sqlserverplanet.com/dmvs/missing-indexes-dmv SELECT mid.statement ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure ,OBJECT_NAME(mid.Object_id) ,’CREATE INDEX [idx_’ + LEFT(PARSENAME(mid.statement, 1), 32) + ‘_’ + CONVERT(VARCHAR, mig.index_group_handle) + ‘_’ + CONVERT(VARCHAR, mid.index_handle) + ‘]’ + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL(mid.equality_columns, ”) + CASE WHEN...
—- 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...
The basic syntax for sending a query to SQL Server using PowerShell. sqlcmd -S SERVER_NAME -d master -Q "select count(1) from sys.objects"