STUFF function in SQL

  Recently in one of our assignment we have utilized STUFF function of SQL Server 2005. So just want to share the same. Stuff is very simple function to delete specified length of symbols and insert new symbols in string. We can say it’s a next version for REPLACE function. Following is simple test environment for STUFF function Above SQL script is simple to understand; so use this in your...

Xml Basics in SQL Server 2005

Xml Basics – SQL Server 2005 Hello All, We have used some basic features for Xml structure in SQL Server 2005 in one of our assignment; just want to share the same. When we heard Xml phenomena in context of SQL Server 2005; it creates lot of curiosity about motivation after using the same. In this article we will concentrate on some Xml standard methods which are part of SQL Server 2005, and...

10 reasons why SQL Server 2008 is going to rock!

Differentiator Features of SQL sever 2008 Here are the top 10 reasons why 10 reasons why SQL Server 2008 is going to rock. 10.  Plug-in model for SSMS.   SSMS 2005 also had a plug-in model, but it was not published, so the few developers that braved that environment were flying blind.  Apparently for 2008, the plug-in model will be published and a thousand add-ins will bloom. ...

SQL Error: Database diagram support objects cannot be installed

Solution to MS SQL Error: Database diagram support objects cannot be installed If you ever get a message like this when trying to create a diagram in SQL 2005 “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...

Delete All Stored Procedures and Views in SQL

  Delete All Stored Procedures declare @procName varchar(500)declare cur cursor for select [name] from sys.objects where type = 'p'open cur fetch next from cur into @procName while @@fetch_status = 0 begin if @procName <> 'DeleteAllProcedures' exec('drop procedure ' + @procName) fetch next from cur into @procName end close...

Delete All Tables in MS SQL

Delete All Tables  – MS SQL Query --Delete All Keys DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSORSET @Cursor = CURSOR FAST_FORWARD FORSELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAMEOPEN @Cursor FETCH...

Default Database roles and there permissions in MS SQL

Default Database roles and there permissions in MS SQL   Database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server: Fixed...

Default MS SQL Server roles and their permissions

Default MS SQL Server roles and their permissions   These are default role available in MS sql sever. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server: Fixed server role Description sysadmin Can...

How to restrict user to access database in MS SQL

RESTRICTED_USER in MS SQL RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER...