Count number of tables in a SQL Server database

I got a request from a user and he wanted to count the number of tables in a database. It's quiet simple. Just use the information_schema.tables

USE YOURDBNAME
SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

' Will return you the count of the tables in the database

Combine Multiple Rows into One Row using SQL Server

Imagine you have a column like this:

Numbers
---------
One
Two
Three
Four
Five

The output you desire is to combine all the rows and put it as one row similar to the following:

OneTwoThreeFourFive


Let us see how to do it:

-- Sample Script to create the table and insert rows
-- By SQLServerCurry.com

CREATE TABLE #Temp
(
[Numbers] varchar(40)
)
INSERT INTO #Temp VALUES('One');
INSERT INTO #Temp VALUES('Two');
INSERT INTO #Temp VALUES('Three');
INSERT INTO #Temp VALUES('Four');
INSERT INTO #Temp VALUES('Five');



-- Query to combine multiple rows into one

DECLARE @str VARCHAR(100)
SELECT @str = COALESCE(@str + '', '') + [Numbers]
FROM #Temp
Print @str

You can also achieve the same result using STUFF

SELECT DISTINCT STUFF( (SELECT '*' + Numbers from #Temp FOR XML PATH('')),1,1,'') as Numbers FROM #Temp

Update: Here's another solution if you want to combine and output multiple rows as CSV - SQL Server: Combine Multiple Rows Into One Column with CSV output

Upgrading to SQL Server 2008

If you are planning to upgrade to SQL Server 2008 and are not sure on the upgrade paths that are supported, then an upgrade matrix has been posted on the BOL.

Note: This link contains a pre-release documentation and is subject to change

SQL Server 2008 Version and Edition Upgrades

Also if you want some info on how to install multiple versions and instances of SQL Server and the side-by-side support for SQL Server 2008, check this link:

Working with Multiple Versions and Instances of SQL Server

Disable all constraints of all tables in a database using SQL Server

If you are looking out for a fast and a simple way of disabling all constraints of all tables in a database using SQL Server, then here is a undocumented stored procedure that helps you achieve the same

USE YOURDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

First weekday of a month in SQL Server

If you are looking out for a query to find the First Weekday of the month, check this out:

DECLARE @varDate DateTime
-- temporary variable to store a day
DECLARE @daynm varchar(10)
-- Date as 6th of May 2008
SET @varDate = '05/06/2008'

SELECT @daynm = DATENAME(dw, DATEADD(dd, - DATEPART(dd, @varDate) + 1, @varDate))
IF( @daynm IN ('Saturday', 'Sunday'))
SET @daynm = 'Monday'
print @daynm

-- returns Thursday

I have chosen the approach to keep the weekday in a variable so as to return it to a calling program. If you do not have such a requirement, you can do the same using a SELECT CASE statement too.

Find all columns with varchar and nvarchar datatypes

If you need to find out all the columns with the varchar and nvarchar datatypes, use this query.

-- List all columns with varchar and nvarchar data type
SELECT OBJECT_NAME(col.OBJECT_ID) as [TableName], col.[name] as [ColName], typ.[name]
FROM sys.all_columns col
INNER JOIN sys.types typ
ON col.user_type_id = typ.user_type_id
WHERE col.user_type_id IN (167,231)
GO

Similarly you can use other usertypeid as shown below to find other datatypes.

bigint 127
binary 173
bit 104
char 175
datetime 61
decimal 106
float 62
image 34
int 56
money 60
nchar 239
ntext 99
numeric 108
nvarchar 231
real 59
smalldatetime 58
smallint 52
smallmoney 122
sql_variant 98
sysname 256
text 35
timestamp 189
tinyint 48
uniqueidentifier 36
varbinary 165
varchar 167
xml 241

Rebuild all indexes of all tables in SQL 2005

In order to rebuild all index on your table, use the DBCC DBREINDEX. DBCC DBREINDEX rebuilds one or more indexes for a table in the specified database.

The syntax for DBCC DBREINDEX is as follows:

DBCC DBREINDEX
(
'table_name'
[ , 'index_name' [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]


where table_name is the table for which the indexes are to rebuild, index_name is the index to be rebuild and fillfactor is % of space on each index page which will be used to store data when the index is rebuilt.

QUERY to rebuild all indexes

USE DBNAME
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO

Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."