Tech Learn

Some times we need to query on the all the tables in one data base single statement. We use SP_MSForeachtable this is known as undocumented stored procedures . These all are system stored procedures. These stored procedures is place in Master database. NOTE: Please do not run all these queries in Production environment Example: create database UnDocumentedStoredProcedure use UnDocumentedStoredProcedure create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 50 ), Salary int ) insert into Emp ( Name , Salary ) values ( 'rakesh' , 8000 ),( 'raju' , 9000 ) create table Dept ( ID int identity ( 1 , 1 ), DeptName varchar ( 100 ) ) insert into Dept ( DeptName ) values ( 'CSE' ),( 'IT' ) We are created new database and also created some table with some dummy data. Select all tables data: exec sp_MSForeachtable 'select * from ?...

creating table with sample data. create table CommaSeparatedList ( ID int identity ( 1 , 1 ) primary key , Name varchar ( 100 ) ) insert into CommaSeparatedList ( Name ) values ( 'rakesh' ),( 'raju' ),( 'ravi' ) Method-1: Using ISNULL : declare @commalist varchar ( max )=null select @commalist = isnull (( @commalist + ',' ), '' )+ cast ( ID as varchar ( max )) from CommaSeparatedList select @commalist output: 1,2,3 Method-2: Using COALESCE : declare @commalist varchar ( max )=null select @commalist = coalesce ( @commalist + ',' , '' )+ cast ( ID as varchar ( max )) from CommaSeparatedList select @commalist output: 1,2,3 Method-3: Using FOR XML PATH : select stuff (( select distinct ',' + cast ( ID as varchar ( max )) from CommaSeparatedList group by ',' + cast ( ID as varchar ( max )) f...

Views are nothing but saved select query. Views nothing but virtual table on top of physical table. Views can contain rows and columns .views is not stored data. The main use of view is hiding some rows data or some columns. There are two types of views: System defined view. User defined view. System Defined view: System defined view is categorized into 3 types System defined views can discuses in future articles. User defined views There are two types of user defined views. Simple View: A simple view is nothing but just single saved select statement. create table Emp ( ID int identity ( 1 , 1 ), Name varchar ( 20 ), Department varchar ( 20 ) ) insert into Emp ( Name , Department ) values ( 'rakesh' , 'software' ),( 'raju' , 'bpo' ),( 'ali' , 'software' ) I have created “Emp” table with above following ...