April 06, 2013

Run query on all databases all tables at once

When I was writing previous post, I was thinking about single line statement to disable all the triggers in SQL Server instance. But after that I found a way to how to do this. Those queries may looks like confusing.

Below query will print all the names of tables in all the databases of SQL Server instance.

EXEC sp_MSforeachdb 'USE [?] EXEC sp_MSforeachtable ''PRINT ''''/'''''',N''/'' '

But the problem here is you have so many ‘ here. And if you want to disable all the triggers on all the databases you can use below query.

EXEC sp_MSforeachdb 'USE [?] EXEC sp_MSforeachtable ''ALTER TABLE / DISABLE TRIGGER ALL'',N''/'' '

This query will disable all the triggers in you SQL Server Instance. But this alter statement will run on temp database’s tables also. In master and msdb you don’t need to worry because all of their tables are System tables. But temp database’s tables are user tables. So that is also not a much worry.


Enjoy SQL Server !

How to run a query on all Tables?

In some cases people need to run some queries against all the databases in their SQL Server instance. Something very very useful for administrators. Lets say somebody want to give read permission on all tables he can use this sp_MSforeachdb
stored procedure.

But what I’m going to tell in this post is not about it. I’m going to talk about sp_MSforeachtable. Let’s say somebody want to disable all the triggers of one database. Then you can use a Script like below.

USE AdventureWorks2012
GO

EXEC
sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
GO

But this command will not run on system tables. This is how I confirmed it.

USE AdventureWorks2012
GO

EXEC
sp_MSforeachtable 'PRINT ''?'''
GO
You can see when you run this query on AdventureWorks database, it will print only user tables. Not on system table of that database.

April 04, 2013

Graph Database පොතක්

graphdatabases_v31

නොම්ලේ ඉගෙන ගන්න අවස්ථාවක්! දැන් ඉතින් ඩේටාබේස් කරන අයටත් ඩිවලොපර්ස් ලටත් NoSQL ඩේටාබේස් ගැන ඇහෙන්න පටන් අරගෙන තියෙන්නේ. ඉතින් මම මේ දවස් වල වැඩියක් ඉගෙන ගන්නේ MongoDB Cassandra Titan(GraphDB) වගේ ඩේටාබේස් ගැන. ඉතින් මේ Graph Database වලින් සමහර බිස්නස් වලට ගොඩක් ලේසියෙන් සොෆ්ට්වෙයාර් හදන්න එහෙමත් පුලුවන්.

Neo4J කියෙන්නේ ඒවගේ Graph Database එකක්. ඉතින් මේ කම්පැනියෙන් නොමිලේ Graph Database ගැන පොතක් ඩවින්ලෝඩි කරගන්න දෙනවා. ඔයාලත් කැමතිනම් ඩව්න්ලෝඩ් කරගෙන කියවලා බලන්න.

http://graphdatabases.com/

April 03, 2013

Free Graph Database Book!

Hey, Do you like to get to know about Graph Database. Then this is for you. Neo4J is giving a Graph Database Book for free. This is a very good opportunity to people who wants to learn about Graph databases. Please don’t miss this!

http://www.neo4j.com/learn/

Enjoy your graph database book!!!

April 02, 2013

MongoDB IDE

I’m using MongoDB for about 6 months. And some guys asked about a IDE. (Basically a GUI tool for MongoDB) At that time there was only one usable GUI Tool. And it is MongoVUE. It is also painful one. So I gave it up. And now I’m  used to MongoDB shell. And I can do everything in MongoDB shell.

But for Developers and QAs to get to know whether their applications are running properly, they need to check MongoDB instances. For them using MongoDB Shell is some thing painful.

MonjaDB

But recently from MongoDB Java Developer course, I got to know about MonjaDB. MonjaDB is a plugin to a Eclipse IDE. And it is really cool IDE. This one is available on any platform where Eclipse is available.

http://marketplace.eclipse.org/content/monjadb#.UVrrrRxHJCY

http://www.jumperz.net/index.php?i=2&a=0&b=9

RoboMongo

This one I just saw one of my colleagues is going to try. This one is not a plugin. And this one also awesome tool. But only available on Macs and Windows. RoboMongo is more into DBAs side. And it supports MongoDB shell as well. And RoboMongo is a combination of set of products. And it supports Intellisense like thing for MongoDB commands

http://www.robomongo.org/ 

Enjoy MongoDB !!!