Are you looking for customized advices? Click here to contact the expert!
Want to advertise here? Contact us!
cghersi
cghersi
Reputation Top 1%
Cristiano Ghersi
Great passion for Software Engineering, mostly coding in Java and C#, I enjoy creating modular and reusable code for everybody!
174 Snippets  (1st place)
Published
13 Channels
Created
54 Channels
Following
yesterday at 5:35:17 PM
Last Visit
May 5, 2012
Registered
5320 points  (1st place)
Reputation
Top 5% Publisher
Junior Code Generator
Serious Code Generator
Senior Code Generator
Master Code Generator
Guru Code Generator
Junior Publisher
Serious Publisher
Senior Publisher
Master Publisher
Guru Publisher
Master Topic Creator
Guru Topic Creator
Junior Topic Hub
Serious Topic Hub
Senior Topic Hub
Master Topic Hub
Junior Trend Maker
Serious Trend Maker
Senior Trend Maker
Senior Team Manager
Junior Influencer
Serious Influencer
Senior Influencer
Junior Commenter
Serious Commenter
Senior Commenter
Master Commenter
Junior Judge
Serious Judge
Senior Judge
Master Judge
Junior Popular Coder
Serious Popular Coder
Senior Popular Coder
Master Popular Coder
Guru Popular Coder
Junior Autobiographer
Serious Autobiographer
Senior Autobiographer
Master Autobiographer
Junior Snip2Coder
Serious Snip2Coder
Senior Snip2Coder
Junior Geek Aggregator
Serious Geek Aggregator
Senior Geek Aggregator
Junior Famous Coder

Recent Snippets See all snippets by cghersi

public by cghersi modified Saturday at 7:19:40 PM  1  3  0

Move a file to another location in SQL Server

Here is the procedure to move a file to another location (e.g. for the database called MyDB). First you need to execute the first part of this snippet (until the comment '/**** Now you need to physically move the file to the other location ***/'). Then you need to move the file and eventually you can execute the last two statements.
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE MyDB SET OFFLINE;

ALTER DATABASE MyDB MODIFY FILE 
(
   Name = MyDB_file1,
   Filename = 'c:\DB\MyDB.mdf'
);

/**** Now you need to physically move the file to the other location ***/

ALTER DATABASE MyDB SET ONLINE;

ALTER DATABASE MyDB SET MULTI_USER;

public by cghersi modified Saturday at 7:16:02 PM  1  3  0

How to Retrieve all the tables having indexes on a specific filegroup in SQL Server

Here are listed all the tables that have one or more indexes stored in a filegroup called 'INDEXES'
SELECT 
    distinct  TableName = t.name
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
INNER JOIN sys.[filegroups] f
    ON f.[data_space_id] = ind.data_space_id
WHERE f.name = 'INDEXES'

public by cghersi modified Saturday at 7:13:45 PM  2  3  0

Find columns used in indexes for SQL Server

Retrieves a number of information related to the structure of the available indexes
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     ColumnName = col.name, FGName = f.name,
	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc, ic.*
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
INNER JOIN sys.[filegroups] f
    ON f.[data_space_id] = ind.data_space_id
WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id

public by cghersi modified Saturday at 7:11:08 PM  3  3  0

Recreate the index structure of an MS SQL Server DB in T-SQL

This script prepares and executes several dynamic SQL statements to recreate indexes exactly as they are built on the original DB
USE Snip
GO

DECLARE @indexName nvarchar(70), @tableName nvarchar(100), @sql nvarchar(4000) = '', @colName nvarchar(100), @desc bit, 
	@inclCol bit, @prevInclCol bit,@unique bit, @typeDesc nvarchar(40), @previousIndexName nvarchar(70) = '',
	@filegroup nvarchar(100) = '[PRIMARY]', @fgName nvarchar(100), @prevFGName nvarchar(100)

DECLARE fk CURSOR FOR 
SELECT 
     TableName = t.name,
     IndexName = ind.name,
     ColumnName = col.name, FGName = '[' + f.name + ']',
	 ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc
FROM 
     sys.indexes ind 
INNER JOIN 
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id 
INNER JOIN 
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id 
INNER JOIN 
     sys.tables t ON ind.object_id = t.object_id 
INNER JOIN sys.[filegroups] f
    ON f.[data_space_id] = ind.data_space_id
WHERE ind.is_unique_constraint = 0  AND t.is_ms_shipped = 0 
ORDER BY 
     t.name, ind.name, ind.index_id, ic.index_column_id

OPEN fk
FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
WHILE @@fetch_status = 0
BEGIN
	IF (@prevFGName = '')
		SET @prevFGName = @fgName;
print 'prev=' + @previousIndexName + ';new=' + @indexName + ';tbl=' + @tableName + ';col=' + @colName + 
	';desc=' + CAST(@desc as nvarchar(10)) + ';fgName=' + @fgName + ';filegroup=' + @filegroup +
	';include=' + CAST(@inclCol as nvarchar(10)) + ';unique=' + CAST(@unique as nvarchar(10)) + ';type=' + @typeDesc
	IF (@previousIndexName = @indexName)
	BEGIN
		--continue with the previous index:
		if (@inclCol = 1 AND @prevInclCol = 0)
			SET @sql += ') INCLUDE ( ' + @colName;
		ELSE
			SET @sql += ',' + @colName;

		SET @prevInclCol = @inclCol;
		print 'sql in equal=' + @sql
	 END
	 ELSE
	 BEGIN
		--complete and exec the last statement:
		SET @prevInclCol = 0;
		IF (LEN(@sql) != 0)
		BEGIN
			IF (@prevFGName != @filegroup)
			BEGIN
				SET @sql += ') ON ' + @filegroup + ';';
				--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
				print 'sql to exec=' + @sql
				EXEC( @sql);
			END
		END

		--create a new statement:
		SET @sql = ' CREATE ';
		IF (@unique = 1)
			SET @sql += ' UNIQUE ';
		SET @sql += @typeDesc + ' INDEX ' + @indexName + '_1 ON dbo.' + @tableName + ' (' + @colName;
		print 'sql created=' + @sql
	END

	IF (@inclCol = 0)
	BEGIN
		IF (@desc = 1)
			SET @sql += ' DESC ';
		ELSE
			SET @sql += ' ASC ';
	END
	SELECT @previousIndexName = @indexName, @prevFGName = @fgName 
				
	FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc
END
CLOSE fk
DEALLOCATE fk

--complete and exec the last statement:
IF (LEN(@sql) != 0)
BEGIN
	IF (@prevFGName != @filegroup)
	BEGIN
		SET @sql += ') ON ' + @filegroup + ';';
		--SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';';
		print 'sql to exec=' + @sql
		EXEC( @sql);
	END
END

public by cghersi modified Oct 14, 2014  1  3  3

How to prettify a number with thousands or millions

Prepares a pretty version of the given number, with the ability to match the right plural/singular form for English text
public static string PaintNumber(int number, string followingText = "", bool numberInBold = false)
{
  string content = String.Format("{0:N0}", number);
  if (number >= 10000)
  {
    double numberInThousands = number / 1000.0;
    if (numberInThousands < 100)
      content = String.Format("{0:N0}k", numberInThousands);
    else
      content = String.Format("{0:0.#}M", numberInThousands / 1000.0);
  }
  if (number >= 1000000)
  {
    double numberInMillions = number / 1000000.0;
    if (numberInMillions < 100)
      content = String.Format("{0:N0}M", numberInMillions);
    else
      content = String.Format("{0:0.#}B", numberInMillions / 1000.0);
  }

  if (numberInBold)
    content = "<b>" + content + "</b>";

  if (!string.IsNullOrEmpty(followingText))
    content += " " + followingText + (number != 1 ? "s" : "");

  return content;
}