Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

Empty all table or delete all data from all table by sp_MSforeachtable in SQL-SERVER 2005

As a developer, many times we need to delete all the records from all tables. For example we are developing one application in our development server. At the time of developing we must have entered lots of temporary data in our database; we shouldn’t upload all those data along with structure at live server. We should have to delete all dummy and temp data.

What will you do in that case? Execute delete statement for all foreign key table first and then delete from master tables????? Or else remove all constraint, check and triggers from all tables and then execute delete statement for all tables. Uuuufffffffff so tedious and boring job!!!! Isn’t it??????

So now we will examine other way of deleting all rows from all tables in short route with the help of sp_MSforeachtable (undocumented stored procedure of Microsoft Sql-Server 2005). 

 let us see, how we can do empty the tables.

CREATE PROCEDURE sp_EmplyAllTable

AS

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

EXEC sp_MSForEachTable ‘DELETE FROM ?’

EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

First we will disable all the constraint in all the tables then we will delete all the records from all the tables and then enable the constraint back again.

How To Obtain The Size Of All Tables In A SQL Server Database

In a current project we were wondering why an application database grew quickly during the last couple of weeks of development and testing. We wanted to know the space used of each table without using Management Studio's table properties.

Actually SQL Server gives you everything you need with its Stored Procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
 
DROP TABLE #t 

The screen shot below shows the results of a smaller testing database.

SQL SERVER – ERROR: FIX – Database diagram support objects cannot be installed because this database does not have a valid owner

Error:
Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Workaround / Fix / Solution :

Well for a while I attempted few things and nothing worked. After that I carefully read the error and I realized that solution was proposed in the error only. I just have to read it carefully. Here are the steps I did to make this work.

-- Replace YourDatabaseName in following scriptALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa
GO

• Select your database >> Right Click >> Select Properties
• Select FILE in left side of page
• In the OWNER box, select button which has three dots (…) in it
• Now select user ‘sa’ or NT AUTHORITY\SYSTEM and click OK.

This should solve your problem.

Please note, I suggest you check your security policies before changing authorization. I did this to quickly solve my problem on my development server. If you are on production server, you may open yourself to potential security compromise.

Install / Uninstall .NET Windows Service [C#]

This example shows how to install and uninstall a .NET windows service.

Install service using InstallUtil.exe

To install or uninstall windows service (which was created using .NET Framework) use utilityInstallUtil.exe. This tool can be found in the following path (use appropriate framework version number).

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe

To install .NET service run command similar to this (specify full path to your service).

InstallUtil.exe "c:\myservice.exe"

To uninstall .NET service use the same command with parameter /u.

InstallUtil.exe /u "c:\myservice.exe"

SQL Server 2008 – Change Edit Top 200 Rows and Select Top 1000 Rows to Select/Edit All

Have you noticed the Context Menu , when you right click on the table in the SQL Server Management Studio Express 2008 .

The SQL Server Management Studio Express 2005 allows us to “Edit” or “View” the entire records in a table.

You might see the following options in the SQL Server Management Studio Express 2008 .

1. Select Top 1000 Rows
2. Edit Top 200 Rows
2. Click the Tools -> Options
3. Select SQL Server Object Explorer . Now you should be able to see the options
* Value for Edit Top Rows Command
* Value for Select Top Rows Command

The idea to include this looks good for performance reasons .But what if you are a person like me who wants want to display all the records .

In SQL Server 2008 Management Studio Express , you can change the default settings thats allows to edit more than the 200 rows at a time, or select more than 1000 rows

To modify the “Edit Top 200 Rows” or “Select Top 1000 Rows” setttings do the following:

1. Run the SQL Management Studio Express 2008

4. Give the Values 0 here to select/ Edit all the Records

5. Now you should see the Edit All and Select All options on the table .

Oops! Google Chrome could not find [name of website]

issue:

Solution:

the majority of page loading problems are a result of the DNS Fetching in Google Chrome.DNS Pre-fetching stores information in the browser to speed up the loading of websites in Chrome. It can, under certain circumstances, result in connection problems...(that can make you curse your laptop/computer). This can happen if a wrong or outdated DNS is stored in the browser for the website in question. You can try emptying the cache, clearing the history files, emptying the %temp% and temp files... but it's useless. What DNS Fetching does is, it speeds up page loading by about 250ms. Chrome users who experience connection difficulties regularly might want to try turning the feature off to see if it resolves the issues.
How to turn off the feature :In the Chrome options. A click on the Wrench icon in the upper extreme right corner of the page and the selection of  Options opens, See the image below for the settings window of the web browser 

Predict network (DNS pre-fetching)

Keep only phishing and malware protection checked [uncheck the others]. Close Options, and restart the web browser. Sometimes this function works and the websites are able to load without any problem. ... well it didn't work for me!!! Arrrrgh !!!!!! But that of course only solves the Chrome issue, if IE, Mozilla, Safari yet shows you the error, and if you see this “Oops! Google Chrome could not find [name of website]

 Try reloading:  [name of website]” in Chrome, this is what you need to do >>>> "Change the DNS settings." Period!!! It is much safer than resetting the router as some do suggest. Also it improves your internet Security.For those unaware, there are many different providers of DNS (Domain Name Servers) available on the Internet, most of them are paid servers. You could try out Google, UltraDNS, OpenDNS etc.Below are the instruction on how to change your DNS settings in XP, and use a universal IP no matter which DNS provider you choose.Changing DNS settings

1. Click Start-Button and Open The

 Control Panel.

1. Locate and open

 Network Connections

1. Double-Click

 your default Network Connection from the available list

1. Click

 Properties

1. Highlight

 Internet Protocol (TCP/IP) and click on Properties again

1. 

1. Click on: “

Use the following DNS server addresses”
The text fields below will be enabled
Type in DNS IP addresses you have obtained from the DNS provider
(you can also use the DNS port numbers as displayed in the image)

The text fields below will be enabled

Type in DNS IP addresses you have obtained from the DNS provider

(you can also use the DNS port numbers as displayed in the image)

The text fields below will be enabled

Type in DNS IP addresses you have obtained from the DNS provider

(you can also use the DNS port numbers as displayed in the image)

1. The text fields below will be enabled

1. Type in DNS IP addresses you have obtained from the DNS provider

1. (you can also use the DNS port numbers as displayed in the image)

1.   

1. Example: 

1. Preferred DNS server address for Open DNS is:

 208.67.222.222

1. Alternate DNS server address for Open DNS is:
 208.67.220.220

1. Click OK

1. Close any open dialogue window and Control Panel

1. Restart all Browsers