List of stored procedures and functions associated with an assembly

In order to get the list of stored procedures and functions associated with an assembly:

SELECT object_name(M.object_id) as SPName, M.assembly_class, M.assembly_method
    sys.assembly_modules M
    INNER JOIN sys.assemblies A ON A.assembly_id = M.assembly_id
WHERE A.name = 'YourAssemblyName'

Index usability - tune your index strategy

The view sys.dm_db_index_usage_stats returns statistical counts of the various index operations that have occurred to an index over time.

     object_name(i.object_id) as TableName
     , i.name as [Index]
     , user_seeks + user_scans + user_lookups + user_updates as usability
     , user_seeks, user_scans, user_lookups, user_updates
     , i.index_id, i.type_desc, i.is_unique, i.is_primary_key, i.is_unique
     sys.indexes i    
     INNER JOIN sys.dm_db_index_usage_stats s ON s.index_id = i.index_id AND s.object_id = i.object_id
     AND i.name is not NULL
     AND s.database_id = DB_ID() -- current DB
     --AND object_name(i.object_id) = '<TableName>'
     usability, [Index], TableName;

How to auto increment a particular column without using IDENTITY?

  • Using sequences if you're using SQL Server 2012
  • Using GUIDs and NEWID() function.
    Non clustered indexes include the clustered index(=PK) as a pointer, that might exclude the GUID option, due to the size of indexes, and reduced performance.
  • Using MAX(yourColumn) within a transaction and setting the right serialization level. However, I'm not a fun of this method.

T-SQL Split function

There are some excellent options available at: http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings, but my favourite solution remains this:

@InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
, @delimiter varchar(10) = ','

DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
SELECT C.value('.', 'varchar(10)') AS value
FROM @xml.nodes('X') as X(C)

How to get random rows from a table?

Did you ever think how to get random rows from a table?Let this article back and try to find a solution on your own. It is not so simple task, isn’t it?Almost sure you thought to JOIN the table with itself, and you might found a reasonable solution.One way to do this would be to:

  1. create a column in the table, put NewId() as its default value (i.e. creates a unique value of type uniqueidentifier.)
  2. sort the table by this column,
  3. then take first x rows, using TOP clause ORDER BY sorts the rows in the table. Combined with the TOP keyword, it ensures that only the first n items in the resultset, as sorted according to the ORDER BY clause, appears in the results. Since you don't have a random column in your table, you might use directly NewId() function, which generates a random GUID, and sorted by that.


How to move transaction log file (.ldf)

Is your drive out of space because your database is growing? Then you might think to move the transaction log by using SQL Server stored procedures for detaching and attaching the database.

For example, to move the pubs database from drive C to drive D:

  1. First use the following sp_detach_db command to detach the database
    EXEC sp_detach_db 'pubs'
  2. Next, copy the pubs_log.ldf file to the destination drive.
  3. Finally, after you copy the file, you can use the sp_attach_db stored procedure to reattach the data to SQL Server.
    EXEC sp_attach_db 'pubs', 'c:\mssql\data\pubs.mdf', 'd:\mssql\data\pubs_log.ldf'

How to show all connected users in SQL Server

The simplest way to list all users and processes is to use the system stored procedure sp_who. To list all active users:

sp_who 'active'

To list details about a particular user:

sp_who 'login-name'

How to check if SQL Server Agent is running

Here it is the T-SQL code snippet:

SELECT program_name, login_time, loginame, cmd, hostname
FROM master.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher'


Subscribe to RSS - sql-server