SQL Server External Tools

SQL Server Management Studio provides a functionality to add external tools(Windows Bases or .Net Based).
This will be in great use when an external executable file has to be run from SSMS.

How to add external tools to SQL Server Management Studio?

The step by step approach to add External tools to SSMS is given below. (Screenshots depict how Notepad has to be added as external tool to SSMS)

Step1:

Open SSSMS

Step2:

Click On Tools Menu Item. Select External Tools… Option

SSMS External Tools Option

Now External Tools Dialog Box pops up. Using this Dialog Box External tools can be added to SSMS.

External Tools Dialog Box
External Tools Dialog Box

Step3:

Now select the command for the external tool by clicking on the browse button.
The below screenshot shows notepad being selected as command from location C:\Windows\System32\notepad.exe

Select Notepad for external tools command
Notepad Selected as External Tools Command

Update the Title with the title you want for this extenal tool(basically the name of the external tool).
If the external tool has arguments then add them in the arguments section.
Also add the intial directory if initial execution directory is needed by the external tool.

For adding Notepad as an external tool there is no mandate to add Arguments and Initial directory.
The tool Title is given as ExternalToolNotepad.

Now click on apply, Then click on OK.

You can find the added external tool in the Tools Menu item.

Using newly added external tool
Using newly added external tool

Now when the external tool is clicked in the tools menu, the tool opens up with the defined options in External tools Dialog.

How to add toolbar Button for External tool?

External tools can be easyly accessed when a toolbar button is added to external tool.
This can be acheived using Customize Option in the tools menu.

Customize External tools
Now Customize dialog box pops up
Select Tools under Catagories in the commands tab.
Drag the External Command1 item in the Commands section to the location on SSMS where you want the button to be added.

Select Tools and External Command1

The cursor initlly has a x mark. As you drag the item the Cursor changes into a + mark indicating it can be added at that location. Leave the cursor at the location where external tool button is needed.

External Tool Added To ToolBar
External Tool Button Added To ToolBar

Using this method External tool can be added to the tool bar, or as a menu item.

restore higher version database backup on lower version sql server – restore problem

when you try to resore higher version database backup of sql server on a lower version of sql server you get an error:

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

10.50.1600 is of higher verion then 10.00.1600.
Also you can get the same error with different versions where higher version is being restored on a lower version database server

This database restore compatability error occurs because you are trying to restore higher version on lower version.

Why this error: Database servers get changed with service packs and new releases. New object types get added and the lower versions cannot understand these object types.
Example: Common Table Expressions used SQL Server 2008 will be unknown for SQL Server 2000.
In order to avoid such conflicts and problems – Higher end database restorations cannot be performed directly on lower end database servers. It is also not possible to Attach the higher version database to lower version sql server.

Following link helps you identify the database backup version Identify SQL Server Database Backup Version

How to restore such databases?

The best way to do restoration is to upgrade the lower version SQL Server to the version greater than or equal to the higher version SQL Server.

There is no direct way to restore the database with out upgrading the server,In such a case following are the alternate ways to restore.

Alternate ways to restore higher database versions on lower version sql servers:

Script the database Objects and restore them on the server
To script the database objects you can use Generate Scripts Task.
Generate Scripts Task can be found in SQL Server management studio.
RightClick on the database -> Click On Tasks -> Then Click on Generate Scripts…
Run through the wizard.
In the set scripting options tab in the generate scripts wizard click on advanced and select the following options:
For ‘types of data to script’ option Select ‘Schema and data’
For ‘Script for version’ select the required target SQL Server version

Generate the scripts and run the generated scripts on the destination server.

You may have to run the script multile times when you face any error.
Create a new database every time you rerun the script.
Fix the errors that you may face due to order in which objects are created (or) data is inserted into tables different tables.

Use SQL Server Import Export Wizard to copy data from source server to destination server (only Data)
You can find Import Export Wizard in SQL Server management studio.

On the Destination Server (Lower database version server)
RightClick on the database -> Click On Tasks -> Then Click on Import.
Give the correct source and destination servers and select all tables.
This creates an SSIS package and select the option to run immedietly if you want to copy data immedietly.

Copy data to destination tables using BCP (only Data)
You can also use BCP to copy data from source server to destination server.
BCP out tables on source server and BCP in on the destination server.

Note: The above methods do not guarantee the similarity between the two databases.

SQL Server Versions:

11.00.1750 11.00.1440 11.00.1103 10.50.2789 10.50.2776 10.50.2772 10.50.2769 10.50.2500 10.50.1807 10.50.1804 10.50.1800 10.50.1797 10.50.1790 10.50.1777 10.50.1769 10.50.1765 10.50.1753 10.50.1746 10.50.1734 10.50.1720 10.50.1702 10.50.1617 10.50.1600 10.50.1352 10.50.1092 10.00.5768 10.00.5766 10.00.5500 10.00.5416 10.00.4323 10.00.4321 10.00.4316 10.00.4285 10.00.4279 10.00.4272 10.00.4266 10.00.4000 10.00.3798 10.00.2850 10.00.2847 10.00.2821 10.00.2816 10.00.2808 10.00.2804 10.00.2799 10.00.2789 10.00.2787 10.00.2775 10.00.2766 10.00.2757 10.00.2746 10.00.2740 10.00.2734 10.00.2723 10.00.2714 10.00.2712 10.00.2710 10.00.2573 10.00.2531 10.00.2520 10.00.1835 10.00.1828 10.00.1823 10.00.1818 10.00.1812 10.00.1806 10.00.1798 10.00.1787 10.00.1779 10.00.1771 10.00.1763 10.00.1750 10.00.1600 10.00.1442 10.00.1300 10.00.1075 10.00.1049 10.00.1019 9.00.5266 9.00.5259 9.00.5254 9.00.5057 9.00.5000 9.00.4912 9.00.4325 9.00.4317 9.00.4315 9.00.4311 9.00.4309 9.00.4305 9.00.4294 9.00.4285 9.00.4273 9.00.4268 9.00.4266 9.00.4262 9.00.4230 9.00.4226 9.00.4224 9.00.4220 9.00.4216 9.00.4211 9.00.4207 9.00.4053 9.00.4035 9.00.4028 9.00.3356 9.00.3355 9.00.3353 9.00.3330 9.00.3328 9.00.3325 9.00.3320 9.00.3318 9.00.3315 9.00.3310 9.00.3301 9.00.3294 9.00.3282 9.00.3260 9.00.3259 9.00.3259 9.00.3257 9.00.3246 9.00.3244 9.00.3240 9.00.3239 9.00.3232 9.00.3231 9.00.3231 9.00.3230 9.00.3228 9.00.3224 9.00.3221 9.00.3221 9.00.3221 9.00.3221 9.00.3215 9.00.3208 9.00.3206 9.00.3200 9.00.3194 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3186 9.00.3182 9.00.3179 9.00.3178 9.00.3177 9.00.3177 9.00.3177 9.00.3175 9.00.3175 9.00.3175 9.00.3175 9.00.3175 9.00.3171 9.00.3169 9.00.3169 9.00.3166 9.00.3166 9.00.3161 9.00.3161 9.00.3161 9.00.3161 9.00.3159 9.00.3156 9.00.3155 9.00.3155 9.00.3155 9.00.3155 9.00.3154 9.00.3154 9.00.3154 9.00.3153 9.00.3152 9.00.3080 9.00.3077 9.00.3073 9.00.3068 9.00.3054 9.00.3050 9.00.3042 9.00.3033 9.00.3027 9.00.3026 9.00.2239 9.00.2237 9.00.2236 9.00.2236 9.00.2234 9.00.2233 9.00.2233 9.00.2233 9.00.2232 9.00.2231 9.00.2230 9.00.2229 9.00.2227 9.00.2226 9.00.2226 9.00.2223 9.00.2221 9.00.2219 9.00.2218 9.00.2216 9.00.2214 9.00.2214 9.00.2211 9.00.2211 9.00.2209 9.00.2208 9.00.2207 9.00.2207 9.00.2207 9.00.2206 9.00.2206 9.00.2206 9.00.2206 9.00.2202 9.00.2201 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2198 9.00.2196 9.00.2196 9.00.2195 9.00.2194 9.00.2192 9.00.2192 9.00.2191 9.00.2190 9.00.2189 9.00.2187 9.00.2181 9.00.2181 9.00.2176 9.00.2176 9.00.2175 9.00.2175 9.00.2175 9.00.2175 9.00.2175 9.00.2174 9.00.2167 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2164 9.00.2156 9.00.2153 9.00.2153 9.00.2050 9.00.2047 9.00.2040 9.00.2029 9.00.1561 9.00.1558 9.00.1554 9.00.1551 9.00.1551 9.00.1550 9.00.1550 9.00.1547 9.00.1545 9.00.1541 9.00.1541 9.00.1539 9.00.1538 9.00.1536 9.00.1534 9.00.1533 9.00.1532 9.00.1531 9.00.1528 9.00.1528 9.00.1528 9.00.1528 9.00.1528 9.00.1519 9.00.1518 9.00.1518 9.00.1518 9.00.1514 9.00.1503 9.00.1502 9.00.1500 9.00.1406 9.00.1399 8.00.2283 8.00.2282 8.00.2279 8.00.2273 8.00.2271 8.00.2265 8.00.2253 8.00.2249 8.00.2248 8.00.2246 8.00.2245 8.00.2244 8.00.2242 8.00.2238 8.00.2236 8.00.2234 8.00.2232 8.00.2231 8.00.2229 8.00.2226 8.00.2226 8.00.2223 8.00.2223 8.00.2218 8.00.2217 8.00.2215 8.00.2215 8.00.2215 8.00.2215 8.00.2209 8.00.2207 8.00.2201 8.00.2199 8.00.2197 8.00.2197 8.00.2197 8.00.2196 8.00.2194 8.00.2194 8.00.2192 8.00.2191 8.00.2191 8.00.2189 8.00.2189 8.00.2187 8.00.2187 8.00.2187 8.00.2187 8.00.2180 8.00.2180 8.00.2175 8.00.2172 8.00.2171 8.00.2168 8.00.2166 8.00.2162 8.00.2159 8.00.2156 8.00.2151 8.00.2151 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2148 8.00.2147 8.00.2145 8.00.2145 8.00.2055 8.00.2040 8.00.2039 8.00.2026 8.00.1547 8.00.1037 8.00.1036 8.00.1035 8.00.1034 8.00.1029 8.00.1027 8.00.1025 8.00.1025 8.00.1024 8.00.1021 8.00.1020 8.00.1019 8.00.1017 8.00.1014 8.00.1014 8.00.1013 8.00.1009 8.00.1007 8.00.1003 8.00.1001 8.00.1000 8.00.997 8.00.996 8.00.996 8.00.994 8.00.994 8.00.994 8.00.993 8.00.993 8.00.993 8.00.991 8.00.990 8.00.988 8.00.985 8.00.980 8.00.977 8.00.973 8.00.972 8.00.970 8.00.967 8.00.962 8.00.961 8.00.959 8.00.957 8.00.955 8.00.954 8.00.952 8.00.952 8.00.952 8.00.949 8.00.948 8.00.944 8.00.937 8.00.936 8.00.935 8.00.934 8.00.933 8.00.929 8.00.928 8.00.927 8.00.926 8.00.923 8.00.922 8.00.919 8.00.916 8.00.915 8.00.913 8.00.911 8.00.910 8.00.908 8.00.904 8.00.892 8.00.891 8.00.879 8.00.878 8.00.876 8.00.876 8.00.876 8.00.873 8.00.871 8.00.871 8.00.870 8.00.869 8.00.866 8.00.865 8.00.865 8.00.863 8.00.863 8.00.859 8.00.858 8.00.857 8.00.857 8.00.857 8.00.856 8.00.854 8.00.852 8.00.852 8.00.851 8.00.850 8.00.850 8.00.850 8.00.848 8.00.847 8.00.845 8.00.845 8.00.844 8.00.842 8.00.841 8.00.840 8.00.840 8.00.839 8.00.839 8.00.837 8.00.837 8.00.837 8.00.837 8.00.837 8.00.819 8.00.818 8.00.818 8.00.818 8.00.818 8.00.818 8.00.816 8.00.814 8.00.811 8.00.811 8.00.811 8.00.807 8.00.804 8.00.801 8.00.800 8.00.800 8.00.800 8.00.798 8.00.794 8.00.794 8.00.794 8.00.794 8.00.791 8.00.790 8.00.789 8.00.788 8.00.781 8.00.780 8.00.780 8.00.780 8.00.779 8.00.776 8.00.775 8.00.769 8.00.769 8.00.765 8.00.765 8.00.765 8.00.765 8.00.763 8.00.762 8.00.760 8.00.743 8.00.743 8.00.741 8.00.736 8.00.735 8.00.733 8.00.730 8.00.728 8.00.725 8.00.725 8.00.723 8.00.721 8.00.721 8.00.718 8.00.715 8.00.715 8.00.714 8.00.713 8.00.710 8.00.705 8.00.703 8.00.702 8.00.701 8.00.701 8.00.700 8.00.696 8.00.696 8.00.695 8.00.695 8.00.695 8.00.693 8.00.690 8.00.689 8.00.688 8.00.686 8.00.682 8.00.679 8.00.678 8.00.667 8.00.665 8.00.661 8.00.655 8.00.652 8.00.650 8.00.644 8.00.608 8.00.604 8.00.599 8.00.594 8.00.584 8.00.578 8.00.578 8.00.561 8.00.558 8.00.558 8.00.552 8.00.552 8.00.534 8.00.532 8.00.475 8.00.474 8.00.473 8.00.471 8.00.469 8.00.452 8.00.444 8.00.444 8.00.443 8.00.428 8.00.384 8.00.296 8.00.287 8.00.251 8.00.250 8.00.249 8.00.239 8.00.233 8.00.231 8.00.226 8.00.225 8.00.223 8.00.222 8.00.218 8.00.217 8.00.211 8.00.210 8.00.205 8.00.204 8.00.194 8.00.190 8.00.100 8.00.078 8.00.047 7.00.1149 7.00.1143 7.00.1143 7.00.1097 7.00.1094 7.00.1094 7.00.1092 7.00.1087 7.00.1079 7.00.1078 7.00.1077 7.00.1063 7.00.1033 7.00.1026 7.00.1004 7.00.996 7.00.978 7.00.977 7.00.970 7.00.970 7.00.961 7.00.921 7.00.919 7.00.918 7.00.917 7.00.910 7.00.905 7.00.889 7.00.879 7.00.857 7.00.843 7.00.842 7.00.839 7.00.835 7.00.776 7.00.770 7.00.745 7.00.722 7.00.699 7.00.689 7.00.677 7.00.662 7.00.658 7.00.657 7.00.643 7.00.623 7.00.583 7.00.517

Online SQL Code Formatter and Beautifier

Today I have added a new online tool to this site.
Link: Free Online SQL Code Formatter and Beautifier

You can use this tool to easily apply beautiful formating to TSQL code.
This tool parses the sql code and formats it.

sql server 2010

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

MICROSOFT SQL SERVER 2010

Microsoft has not released any SQL Server version with the version name ‘SQL Server 2010’.

However there were two major releases in the year 2010.

Version Year Release Name Codename
10.25 2010 SQL Azure Matrix (aka CloudDB)
10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)

The next release name for ms sql server is SQL Server 2012 with code name denali.

Versions of SQL Server can be found in this link: sql-server-history

More about sql server 2012 can be found here: denali

Download links for SQL Server 2012 can be found here: sql-server-2012-rc0-download-link

This Post answers the questions for the following terms:

sql server 2010 release date : There is no such release date.
ms sql server 2010 : No such product.
sql server 2010 beta : No such product.
sql server 2010 express : No such product.
sql server 2010 download : No such product.
microsoft sql server 2010 : No such product.
microsoft sql server 2010 release date : There is no such release date.
microsoft sql server 2010 express : No such product.
microsoft sql server 2010 download : No such product.
download sql server 2010 : No such product.
sql server 2010 features : No such product.
sql server 2010 management studio : No such product.
sql server 2010 r2 : No such product.
sql server 2010 developer edition : No such product.
sql server 2010 download : No such product.
latest version of sql server 2010 : No such product.
ms sql server 2010 release date : There is no such release date.
sql server 2010 access : No such product.
sql server 2010 release : No such release name.
sql server 2010 pricing : No such product.

References: wiki

Understanding SOME ANY ALL Operators with examples

These operators involve a query and a Sub Query.
QUERY (CONDITION) SOME|ANY|ALL (SUB QUERY)

CONDITION Above would include the following operators
= (or) (or) ! = (or) > (or) > = (or)! > (or) < (or) < = (or) ! <

SOME Operator: This operator compares the value in the query with the values in the sub query. It returns true if the value condition matches with any of the results in the sub query.
ANY operator is same as SOME Operator.

Example of using some (or) any operator:
[sql]
DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

IF 2 > SOME (SELECT ID FROM @vTable)
PRINT ‘Returned True’
ELSE
PRINT ‘Returned False’

IF 3 < ANY (SELECT ID FROM @vTable)
PRINT ‘Returned True’
ELSE
PRINT ‘Returned False’
[/sql]

In the above query 2 greater some of 1,2 and 3.
So 2 when compared with 1, 2 is greater.
Hence the result is true.

In the next statement 3 is lesser than any of 1,2 and 3.
So when compared to each of it none of them return true,
Hence the result is false.

ALL Operator: This operator compares the value in the query with the values in the sub query. It returns true if the condition matches with all of the results in the sub query.

Example of using ALL operator:
[sql]
DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

IF 4 > ALL (SELECT ID FROM @vTable)
PRINT ‘Returned True’
ELSE
PRINT ‘Returned False’

IF 3 > ALL (SELECT ID FROM @vTable)
PRINT ‘Returned True’
ELSE
PRINT ‘Returned False’
[/sql]

In the above query 4 greater than all of 1,2 and 3.
So 4 when compared with all of 1, 2 and 3 we can find 4 is greater.
Hence the result is true.

In the next statement 3 greater than all of 1,2 and 3.
So when compared to each of it 3 is greater than 1,2 but is lesser than 3.
So 3 is not greater than all of 1,2,3.
Hence the result is false.

using max aggregate function on varchar column vs int column sql server

MAX aggregate Function behaves in a different way on numeric columns when compared to alpha-numeric columns.

consider the below TSQL script:
[sql]
DECLARE @vTable TABLE (ID INT)

INSERT INTO @vTable(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable
[/sql]

We can expect the result of the MAX(ID) to be 199.

Now consider this TSQL Script:
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT MAX(ID) FROM @vTable1
[/sql]

Now if you expect the result to be 199 again then you are wrong.
The result is 99.

Why this difference:
ID in the second script is a varchar column.
so for varchar being alpha numeric the ascii codes start with NULL for dec(0) till dec(255).
1 comes at around dec(49), 9 comes at dec(57).
So it compares 1 in 199 with 9 in 99 and sorts out that 99 is greater.
Hence the result 99.

Below query you can see the data in sorted format on the ID (varchar) column.
[sql]
DECLARE @vTable1 TABLE (ID varchar(10))

INSERT INTO @vTable1(ID)
SELECT 1
UNION
SELECT 199
UNION
SELECT 99

SELECT ID FROM @vTable1 order by ID asc
[/sql]

Results in sql server management studio:
1
199
99

Operations On All Tables In All Databases in SQL Server using sp_MSforeachdb and sp_MSforeachtable

Queries that are used to perform various operations on all the tables on all the databases can be simplified by using the Undocumented stored procedures sp_MSforeachdb and sp_MSforeachtable.
Combined use of both sp_MSforeachdb and sp_MSforeachtable will make the tasks simpler.

Example tsql queries:
Note: The below queries ignores system databases like master,model etc. Add the other databases to ignore in the if clause.

Query1:
TSQL Query to find the total number of rows in all the tables in all the databases in sql server.
[sql]
CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255),
unused VARCHAR(255) );

EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”INSERT INTO #Tables EXEC sp_spaceused ””&”””,
@replacechar = ”&”

SELECT SUM(rows) from #Tables
SELECT MAX(rows) from #Tables
[/sql]

The above query gives two resultsets first on is the sum of all the rows in all the databases.
Second on is the maximum number of rows in all the databases.
& is used as a replace charecter for sp_MSforeachtable, this is done as both the procedures sp_MSforeachdb and sp_MSforeahtable have ? as default value.

In the above query:
– The insert clause is executed for each table in the database by sp_MSforeachtable.
– The sp_MSforeachtable clause is executed for each database by sp_MSforeachdb.

Query2:
TSQL Query to select all the records from all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”SELECT * FROM &”,
@replacechar = ”&”

[/sql]

Query3:
TSQL Query to drop all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”DROP TABLE &”,
@replacechar = ”&”

[/sql]

Query4:
TSQL Query to truncate all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
EXEC [?].dbo.sp_MSforeachtable
@command1 = ”TRUNCATE TABLE &”,
@replacechar = ”&”

[/sql]

Query5:
TSQL Query to update Statistics on all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
BEGIN
PRINT ”Started Processing Database ?”
EXEC dbo.sp_MSforeachtable
@command1 = ”
PRINT ””Started Update Statistics on ?.&””
UPDATE STATISTICS &”,
@replacechar = ”&”
END

[/sql]

Query5:
TSQL Query to enable change tracking on all the tables in all the databases(non system)
[sql]
EXEC sp_MSforeachdb
@command1 = ‘
IF not exists(select 1 where ”?” in (”master”,”model”,”msdb”,”tempdb”))
BEGIN
PRINT ”Started Processing Database ?”
EXEC dbo.sp_MSforeachtable
@command1 = ”
PRINT ””Started ENABLE CHANGE TRACKING on ?.&””
ALTER TABLE &
ENABLE CHANGE_TRACKING”,
@replacechar = ”&”
END

[/sql]

Read more about using sp_msforeachdb and
using sp_msforeachtable

sp_MSforeachtable Examples – Uses – InDetail – msdn

sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online.

Use:
sp_MSforeachtable can be used to run a query against each table in the database.

Examples:
Updating statestics for each table in the database
Logging the space used by each table in the database
finding the number of rows for each table in database

Syntax:
[sql]
EXEC sp_MSforeachtable @command1,
@replacechar,
@command2,
@command3,
@whereand,
@precommand,
@postcommand
[/sql]
@command1 – Is the command that hs to be run against each table.
@replacechar – Is the replace charecter for table name. Default value is ?
@command2 – This is the second command that is run after @command1.
@command3 – This is the third command that is run after @command1 and @command2.
@whereand – This is the where clasue that can be used to filter tables.
@precommand – This command is run first by the procedure.It is run before processing any table
@postcommand – This command is run last by the procedure.It is run after processing all the tables.

Note: ? Can be used for table name in @command1,@command2 and @command3.

Example TSQL Queries:

Query1:
Query to list all the tables in the database
[sql]
EXEC sp_MSforeachtable ‘SELECT ”?”’
[/sql]
? – Represents table name
”?” is used as the name of the table is a string and has to be opened and closed in quotes in select clause.

Query2:
Query to save and list all the tables in the database:
[sql]
CREATE TABLE #Tables (TableName VARCHAR(255))
EXEC sp_MSforeachtable ‘INSERT INTO #Tables SELECT ”?”’
SELECT * FROM #Tables
[/sql]
Table names are saved into the temporary table #Tables.
Note: There will be a chage in resultsets for query1 and query2.
You can observe that you get multiple resultsets one for each table with query1 and a single resultset containing all table names for query2.

Query3:
Query to update statistics of all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘UPDATE STATISTICS ?’
[/sql]

The above query executes below commands if you are using Adwentureworks.
[sql]
UPDATE STATISTICS [Sales].[Store]
UPDATE STATISTICS [Production].[ProductPhoto]
…. etc
[/sql]

Query4:
Query to calculate the space used for each table.
[sql]
CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255),
unused VARCHAR(255) );

EXEC sp_MSforeachtable ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’
SELECT * FROM #Tables

SELECT sum(rows) from #Tables
[/sql]

The first resultset would be the detailed space used data for each table.
The second resultset will give the total number of rows in all the tables in the database.

Query5:
Using where clause with sp_MSforeachtable(@whereand clause).
Query to execute sp_spaceused procedure and fetch the resulset of all the tables in the database that begin with p.
[sql]
–drop table #Tables
CREATE TABLE #Tables (
TableName VARCHAR(255),
rows int,
reserved VARCHAR(255),
data VARCHAR(255),
index_size VARCHAR(255),
unused VARCHAR(255) );

EXEC sp_MSforeachtable
@command1 = ‘INSERT INTO #Tables EXEC sp_spaceused ”?”’,
@whereand = ‘ and object_name(object_id) like ”p%” ‘

SELECT * FROM #Tables
[/sql]

Query6:
Query to delete data in all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘delete ?’
[/sql]

Query7:
Query to disable triggers on all tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started disabling triggers on table ?”,1,1)
ALTER TABLE ? DISABLE TRIGGER ALL ‘
[/sql]

Query8:
Query to truncate all the tables in the database.
[sql]
EXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’
[/sql]

Query9:
Query to disable constaints on all tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started disabling constraints on table ?”,1,1)
ALTER TABLE ? NOCHECK CONSTRAINT ALL’
[/sql]

Query10:
Query to enable change tracking on all the tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started enabling change tracking on table ?”,1,1)
ALTER TABLE ? ENABLE CHANGE_TRACKING;’
[/sql]

Query11:
Query to rebuild clustered index/all non clusted indexes on all the tables
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started rebuild index on table ?”,1,1)
ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE);’
[/sql]

Query12:
Query to drop all the tables in the database
[sql]
EXEC sp_MSforeachtable ‘
RAISERROR(”Started dropping table ?”,1,1)
DROP TABLE ?’
[/sql]

SQL Server 2012 RC0 – Download Link – Product Guide Link

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

SQL Server 2012 RC0 Download Links
SQL Server 2012 RC0 Download
Download Product Guide
Download Whitepaper
Download Data Sheet
CTP3
Virtual Labs for Learning
Virtual Labs for Learning
Microsoft Official Site

SQL Server Error Messages List

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

SQL Server error messages can be found in sysmessages table in the master database.
Following are the columns of sysmessages table.

Column_name Type
error int
severity tinyint
dlevel smallint
description nvarchar
msglangid smallint

Following pages that contain list of sql server error messages.