tag:blogger.com,1999:blog-86658242359195153642024-03-08T09:30:50.288-08:00SQL SERVER Latest Informationajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.comBlogger24125tag:blogger.com,1999:blog-8665824235919515364.post-21017799856353390342020-05-08T15:25:00.001-07:002020-05-08T15:25:58.464-07:00About Quenchhunger Food Delivery AAP..<iframe allowfullscreen="" frameborder="0" height="270" src="https://www.youtube.com/embed/R_nFdCl1hxc" width="480"></iframe>ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-38837889637476803882015-07-22T13:46:00.002-07:002015-07-22T13:46:21.557-07:00How to Enable or Disable Trigger in Sql Server 2008/2008R2<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<pre style="font-family: Consolas, Courier, monospace !important; font-size: 13px; line-height: 17.5499992370605px; overflow: auto; padding: 5px; word-wrap: normal;">USE Database
GO
DISABLE TRIGGER Trigger_Name ON Table_Name;
GO</pre>
</div>
ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com2tag:blogger.com,1999:blog-8665824235919515364.post-36499065064090851612013-05-16T23:18:00.001-07:002013-05-16T23:18:41.268-07:00How to Change the SQL Server Instance Name after Renaming the Windows Host<div dir="ltr" style="text-align: left;" trbidi="on">
First to check your Existing Server Name by using -select @@servername<br />
Result-ABC<br />
<br />
Second Step-Sp_dropserver 'ABC'<br />
<br />
Third Step-sp_Addserver 'Ajay'</div>
ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-38737931088168246952011-01-28T19:48:00.000-08:002011-01-28T19:49:26.806-08:00Get the Basic Idea of Cursor from Given Written CursorSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO
<br />
<br />CREATE proc GET_BIN_RES_A --123--1024--123--668--1363--1128--777--255--33--1137--1386--1137--33--1061 --138--1137--289--99--255--1061--1373 --Get_Bin_cursor @Item int as Declare @rv_no varchar(30) Declare @rv_no1 varchar(30) Declare @rv_no2 varchar(30) Declare @rv_no3 varchar(30) Declare @iv_no varchar(30) Declare @iv_no1 varchar(30) Declare @iv_no2 varchar(30) Declare @rv_date smalldatetime Declare @iv_date1 smalldatetime Declare @iv_date2 smalldatetime Declare @iv_date smalldatetime Declare @rv3 smalldatetime Declare @vendor_name varchar(200) Declare @client_name varchar(200) Declare @client_name1 varchar(200) Declare @bill_no int Declare @podate smalldatetime Declare @bin_no int Declare @itemdesc varchar(250) Declare @pono varchar(100) --Declare @pono varchar(100), declare @recd_qty int declare @issue_qty int declare @issue_qty1 int declare @item_receipt int Declare @Balance int Declare @potrate int Declare @Bal int Declare @po1 varchar(100) declare @aa int Declare @Bal1 int Declare @Bal11 int Declare @Bal12 int Declare @Bal2 int Declare @Bal3 int Declare @Bal4 int Declare @Bal5 int Declare @Bal16 int Declare @Bal17 int Declare @Bal18 int Declare @item_id int Declare @aa12 int Declare @aa1 int Declare @mes varchar(100)Declare @Pack_Size_G varchar(200)declare @aa51 intdeclare @sr1 intdeclare @sr2 intdeclare @cnt intDECLARE bin_cursor CURSOR FOR Select rv_no,rv_date,vendor_name,podate,grnitemid as bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,grnitemid as item_id,mes,Pack_Size_G from Get_BIN_RV_A where <a href="mailto:grnitemid=@Item">grnitemid=@Item</a> order by rv_date delete from tem_bin open bin_cursor FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G set @po1=@pono set @rv_no1=@rv_no -- WHILE @@FETCH_STATUS = 0 BEGIN
<br /> if @rv_no=@rv_no1 begin set @Balance=@recd_qty <a href="mailto:+@item_receipt">+@item_receipt</a> insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@Balance,@item_id,@mes,@Pack_Size_G) end
<br /> if @rv_no<>@rv_no1 begin select @bal4=balance from tem_bin where sr in (select max(sr) as sr from tem_bin)--item_id=@item set @bal3=@Bal4+@recd_qty print 'BaliR' + ' ' + convert(varchar(10),@bal3) insert into tem_bin(rv_no,rv_date,vendor_name,podate,bin_no,itemdesc,pono,recd_qty,item_receipt,potrate,Balance,item_id,mes,Pack_Size_G) values(@rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@bal3,@item_id,@mes,@Pack_Size_G) --select @rv3=max(rv_date) from tem_bin where rv_date <<a href="mailto:=@rv_date">=@rv_date</a> -- print @rv3--set @sr1=0select @sr1=max(sr) from tem_bin where <a href="mailto:item_id=@item">item_id=@item</a> --sr in (select max(sr) as sr from tem_bin)select distinct @rv3=max(rv_date) from Get_BIN_RV_A where <a href="mailto:grnitemid=@item">grnitemid=@item</a> --and rv_date<@rv_date -- in (select rv_no as rv_no from tem_bin)
<br />print @rv3 print @sr1 end
<br />DECLARE binA_cursor CURSOR FOR Select iv_no,iv_date,issue_qty,clientname,itemdesc,item_id from GET_BIN_IV where <a href="mailto:item_id=@Item">item_id=@Item</a> --order by iv_date open binA_cursor FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id set @iv_no2=@iv_no WHILE @@FETCH_STATUS =0 begin if @iv_no=@iv_no2 --break begin select top 1 @iv_no=iv_no,@issue_qty=issue_qty,@iv_date=iv_date,@client_name=clientname from GET_BIN_IV where <a href="mailto:item_id=@item">item_id=@item</a> --order by iv_date--and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> set @aa12=(Select count(rv_no) as rv_no from Get_BIN_RV_A where <a href="mailto:grnitemid=@Item">grnitemid=@Item</a> ) -- select @rv3=max(rv_date) from tem_bin where rv_date <<a href="mailto:=@rv_date">=@rv_date</a> -- print @rv3 if @aa12=1 begin select @rv3=max(rv_date) from tem_bin where rv_date <<a href="mailto:=@rv_date">=@rv_date</a> print 'aa5' if (@item_receipt <a href="mailto:+@recd_qty)">=@issue_qty">+@recd_qty)>=@issue_qty</a> -- if 41>=@issue_qty print'check' -- if 41>=@issue_qty begin set @bal=@balance-(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) -- update tem_bin set <a href="mailto:issue_qty=@issue_qty">issue_qty=@issue_qty</a> ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date <a href="mailto:=@rv3">=@rv3</a> --and <a href="mailto:item_id=@item">item_id=@item</a> --and rv_date>@rv_date --and <a href="mailto:rv_no=@rv_no">rv_no=@rv_no</a>-- <a href="mailto:item_id=@item">item_id=@item</a> --and-and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> update tem_bin set <a href="mailto:issue_qty=@issue_qty">issue_qty=@issue_qty</a> ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where <a href="mailto:rv_date=@rv3">rv_date=@rv3</a>-- sr in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and <a href="mailto:rv_no=@rv_no">rv_no=@rv_no</a>-- <a href="mailto:item_id=@item">item_id=@item</a> --and-and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> end end if @aa12>1 begin print 'ajay' set @bal=@bal3 -(case when isnull(@issue_qty,'')='' then 0 else @issue_qty end) if @rv_date=@rv3 begin update tem_bin set <a href="mailto:issue_qty=@issue_qty">issue_qty=@issue_qty</a> ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where <a href="mailto:sr=@sr1--rv_date">sr=@sr1--rv_date</a> <a href="mailto:=@rv3">=@rv3</a> --and <a href="mailto:item_id=@item">item_id=@item</a> --and rv_date>@rv_date --and <a href="mailto:rv_no=@rv_no">rv_no=@rv_no</a>-- <a href="mailto:item_id=@item">item_id=@item</a> --and-and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> endelse begin update tem_bin set <a href="mailto:issue_qty=@issue_qty">issue_qty=@issue_qty</a> ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where rv_date <a href="mailto:=@rv3">=@rv3</a> --and <a href="mailto:item_id=@item">item_id=@item</a> --and rv_date>@rv_date --and <a href="mailto:rv_no=@rv_no">rv_no=@rv_no</a>-- <a href="mailto:item_id=@item">item_id=@item</a> --and-and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> end end end
<br />------iv no not same---------------------------------------------------------------------------------if @iv_no<>@iv_no2 begin print 'ajay1' select @bal3=balance from tem_bin where sr in (select max(sr) as sr from tem_bin) set @Bal5=@bal3-@issue_qty set @cnt=(select count(iv_no) as iv_no from get_bin_iv where <a href="mailto:item_id=@item">item_id=@item</a> and iv_no in (select iv_no from tem_bin where isnull(iv_no,'')!='' ) and <a href="mailto:item_id=@item">item_id=@item</a>) -- set @cnt=(select count(iv_no) as iv_no from get_bin_iv where <a href="mailto:iv_no=@iv_no2">iv_no=@iv_no2</a>)-- in (select iv_no from tem_bin where <a href="mailto:item_id=@item">item_id=@item</a> and isnull(iv_no,'')!='')) if @bal5<0>=1 begin print 'ajayaaaaaaa' insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) update tem_bin set <a href="mailto:item_receipt=@item_receipt">item_receipt=@item_receipt</a> end if @cnt=0 begin print 'break1' break end
<br />--else-- begin -- print'aa' -- if @cnt>=2 -- begin -- insert into tem_bin(iv_no,issue_qty,iv_date,clientname,balance,itemdesc,item_id) values(@iv_no,@issue_qty,@iv_date1,@client_name1,@bal5,@itemdesc,@item_id) -- update tem_bin set <a href="mailto:item_receipt=@item_receipt">item_receipt=@item_receipt</a> -- end
<br /> -- end
<br />
<br />end------------------------------------------------------------------------------------------------------
<br /> FETCH NEXT FROM binA_cursor INTO @iv_no,@iv_date1,@issue_qty,@client_name1,@itemdesc,@item_id end CLOSE binA_cursor DEALLOCATE binA_cursor
<br /> FETCH NEXT FROM bin_cursor INTO @rv_no,@rv_date,@vendor_name,@podate,@bin_no,@itemdesc,@pono,@recd_qty,@item_receipt,@potrate,@item_id,@mes,@Pack_Size_G
<br />end --update tem_bin set <a href="mailto:issue_qty=@issue_qty">issue_qty=@issue_qty</a> ,iv_no=@iv_no,balance=@bal,iv_date=@iv_date,clientname=@client_name where sr <a href="mailto:=@sr1--@sr1">=@sr1--@sr1</a> --in (select max(sr) as sr from tem_bin ) --and rv_date>@rv_date --and <a href="mailto:rv_no=@rv_no">rv_no=@rv_no</a>-- <a href="mailto:item_id=@item">item_id=@item</a> --and-and <a href="mailto:grn_no=@rv_no">grn_no=@rv_no</a> CLOSE bin_cursor DEALLOCATE bin_cursor
<br />
<br />GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
<br />
<br />ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-8423454905930490112011-01-28T05:04:00.000-08:002011-01-28T05:07:36.319-08:00Table Row Count Easily in Sql server 2000/@005Sql Server 2000<br />SELECT o.NAME, i.rowcnt FROM sysindexes AS i INNER JOIN sysobjects AS o ON i.id = o.id WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0 ORDER BY o.NAME<br /><br />Sql Server 2005<br />SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id < 2 AND o.is_ms_shipped = 0 ORDER BY o.NAMEajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-58281166355410912802011-01-10T00:14:00.000-08:002011-01-10T00:16:26.029-08:00Get File Statistics Using fn_virtualfilestatsFrom below given Query -<br />you will get many valuable information like what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes. Due to IO if there has been any stall (delay) in read or write, you can know that as well.<br /><br />SELECT DB_NAME(vfs.DbId) DatabaseName, mf.name,<br />mf.physical_name, vfs.BytesRead, vfs.BytesWritten,<br />vfs.IoStallMS, vfs.IoStallReadMS, vfs.IoStallWriteMS,<br />vfs.NumberReads, vfs.NumberWrites,<br />(Size*8)/1024 Size_MB<br />FROM ::fn_virtualfilestats(NULL,NULL) vfs<br />INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId<br />AND mf.FILE_ID = vfs.FileIdajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-20657182010211868562011-01-07T11:20:00.000-08:002011-01-07T11:21:38.581-08:00SSIS in sql server 2005SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. While ETL processing is common in data warehousing (DW) applications, SSIS is by no means limited to just DW; e.x. when you create a Maintenance Plan using SQL Server Management Studio (SSMS) an SSIS package is created. At a high level, SSIS provides the ability to:<br />retrieve data from just about any source<br />perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.<br />load data into just about any source<br />define a workflow<br />The first version of SSIS was released with SQL Server 2005. SSIS is a replacement for Data Transformation Services (DTS) which was available with SQL Server 7.0 and SQL Server 2000. SSIS builds on the capabilities introduced with DTS.<br />In this tutorial we will step through a number of topics that you need to understand in order to successfully build an SSIS package. Our high level outline is as follows:<br />Creating SSIS packages with SQL Server Management Studio (SSMS)<br />Business Intelligence Development Studio (BIDS)<br />Creating a simple SSIS package in BIDS<br />Deploying SSIS packages<br />Executing SSIS packagesajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-79639595719799563892011-01-07T11:13:00.000-08:002011-01-07T11:14:33.878-08:00Different Way of Alias in columnThere are different ways to alias a column. Here are some possible ways that work in SQL Server.<br />01. select<br />02. 1 as number,<br />03. 1 number,<br />04. 1 "number",<br />05. 1 'number',<br />06. 1number,<br />07. 1"number",<br />08. 1'number',<br />09. 1 as "number",<br />10. 1 as 'number',<br />11. 1.number,<br />12. 1."number",<br />13. 1.'number',<br />14. 1 as [number],<br />15. 1 [number],<br />16. 1.[number],<br />17. 1[number]ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-89748766211978676062011-01-07T10:54:00.000-08:002011-01-07T10:59:24.456-08:00Bulk Insert in sql server 2000Bulk Insert-Copies a data file into a database table or view in a user-specified format.<br />Eg-<br />BULK INSERT test<br /> FROM 'd:\testabc.txt'<br /> WITH<br /> (<br /> FIELDTERMINATOR =',',<br /> ROWTERMINATOR = '\n'<br /> )ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-42459245455501820482011-01-05T16:19:00.000-08:002011-01-05T16:23:45.610-08:00Break vs ReturnSuppose you have a set of codes and when a particular condition is true, you want to terminate or switch to another set of code, you can use Break commnad. I see sometimes people use return commnad for this. But there is a significant difference between these two. Consider this example<br />Usage of BREAK (select will be executed)<br />declare @j int<br />set @j=1<br />while 1=1<br />begin<br /> print @i<br />if @j>10 break -It Terminate the Current Block<br />set @j=@j+1<br />end<br />select @j<br /><br />declare @i int<br />set @i=1<br />while 1=1<br />begin<br />print @i<br />if @i>10 return It Terminate the Entire Block<br />set @i=@i+1<br />end<br />.select @i<br />Break Command-it just terminate the current block (created by if clause, while loop etc), but Return Command- will terminate the entire blockajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-49085681651829950302011-01-05T16:11:00.000-08:002011-01-05T16:13:16.843-08:00Retrieving all the table Name and view name of specific Databaseuse ABC<br />select * from information_schema.tables-Retrieve Tables<br /><br />select * from information_schema.view --retrieve Viewsajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-15169358117873245722011-01-05T16:04:00.000-08:002011-01-05T16:06:27.487-08:00OPENROWSETOPENROWSET-Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.<br /><br />Eg-SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=F:\', testing#txt)ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-76978341590510358502011-01-05T15:59:00.000-08:002011-01-05T16:03:02.144-08:00List of datatypes supported in SQL ServerList of datatypes supported in SQL Server-<br />exec sp_datatype_info in sql server 2000<br />exec sp_datatype_info_90 in sql server 2005<br />exec sp_datatype_info_100 in sql server2008ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-28099709190336160642009-06-09T12:06:00.000-07:002009-06-09T12:09:36.239-07:00use of cursor to print all Stored procedure of Database<p> DECLARE @procName varchar(100)</p><p>DECLARE RetProcName CURSOR FOR select [Name] from sysobjects where type='P'</p><p> OPEN RetProcName</p><p>FETCH NEXT FROM RetProcName INTO @procName</p><p>WHILE @@FETCH_STATUS = 0</p><p>BEGIN </p><p>exec sp_helptext @Procname</p><p>-- print</p><p>Fetch NEXT FROM RetProcName INTO @procName</p><p>END</p><p>CLOSE RetProcName</p><p>DEALLOCATE RetProcName</p>ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-21312649500307989722009-06-09T09:22:00.000-07:002009-06-09T09:29:48.985-07:00IDENT_CURRENT and dbcc CheckidentIDENT_CURRENT-><br />Returns the last identity value generated for a specified table in any session and any scope.<br />Select ident_current('Table_Name')<br />-> Checks the current identity value for the specified table and, if needed, corrects the identity value.<br />By using Following Syntax-<br />dbcc checkident (Table Name,Reseed,1020000)ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-2080193413540386392009-06-09T03:11:00.000-07:002009-06-09T03:15:00.408-07:00How to Refresh Your all the view in single Command in Sql Server<p>DECLARE @TabName varchar(100)</p><p>Declare @T1 varchar(100)</p><p>DECLARE Refereshview CURSOR FOR select table_name from information_schema.views </p><p>OPEN Refereshview</p><p>FETCH NEXT FROM Refereshview INTO @TabName</p><p>WHILE @@FETCH_STATUS = 0</p><p>BEGIN</p><p>set @t1=@TabName</p><p>exec sp_refreshview @t1</p><p>print @t1</p><p>Fetch NEXT FROM Refereshview INTO @TabName</p><p>END</p><p>CLOSE Refereshview</p><p>DEALLOCATE Refereshview </p>ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-60187077752116454742009-06-09T02:52:00.000-07:002009-06-09T02:56:08.406-07:00Findout the information about current users and processes of sql serverSyntax-> SP_WHO Login_Name<br />Or You can Use Sp_Who for all User .ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-17544820291804116912009-06-09T00:19:00.000-07:002009-06-09T12:05:19.044-07:00Recreate IndexDECLARE @TabName varchar(100)<br />Declare @T1 varchar(100)<br />DECLARE Recreateindex CURSOR FOR select table_name from information_schema.tables OPEN Recreateindex<br />FETCH NEXT FROM Recreateindex INTO @TabName<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />set @t1=@TabName<br />DBCC DBREINDEX (@T1)<br />-- print<br />Fetch NEXT FROM Recreateindex INTO @TabName<br />END<br />CLOSE Recreateindex<br />DEALLOCATE Recreateindexajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-19544245235825385562009-06-09T00:05:00.000-07:002009-06-09T00:09:56.008-07:00Find out the information about a specified database or all databases.Sp_helpdb<br />->Information about the all databases.<br />Sp_helpdb Database Name<database><br />->Information About the Specified database.ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-61000517359147590592009-06-09T00:00:00.000-07:002009-06-09T00:01:58.255-07:00How to find out column information for the specified tables or viewsSyntax -> Sp_columns TableName or View Nameajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-49720307570491329512009-06-08T23:22:00.000-07:002009-06-08T23:35:23.229-07:00How to use Table Variable and advantage of Table variableSyntax of Table Variable-<br />DECLARE @TestTable TABLE<br />(<br /> Pid int,<br /> Pdesc varchar(200)<br />)<br />You can use table variables in batches, stored procedures, and user-defined functions (UDFs). We can UPDATE records in our table variable as well as DELETE records.<br />Update @TestTable Set Pdesc ='PEN' where Pid =1<br />Select * from @TestTable<br />Advantage-It provide less locking and logging overhead than table Variable.ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-64145287636061418262009-06-08T22:53:00.000-07:002009-06-08T23:05:51.556-07:00How to find Depended Table in view and Proceduresp_depends View Name /Procedure Name<view><br />This Sql Procedure Helps to find out the Dependent Tables and columns in View or Procedure.ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-59401528297206747722009-06-07T11:51:00.000-07:002009-06-07T11:56:21.885-07:00Query for Count Double Records in a TableDeclare @c_no int<br />Set @c_no=1<br />select id1,count(id1) as cnt from tab1<br />group by id1<br />having count(id1)>@C_No<br />Note: Result will come this way-><br />id1 cnt<br />101 2<br />103 2ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0tag:blogger.com,1999:blog-8665824235919515364.post-71698712426598639582009-06-07T03:38:00.000-07:002009-06-07T04:14:18.538-07:00Add Null and string in sql server 2000<strong>Function for adding Null and string :SET CONCAT_NULL_YIELDS_NULL { ON OFF } </strong><br />When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result.<br /><br />CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself .<br /><p>SET CONCAT_NULL_YIELDS_NULL OFF </p><p>Select ' ' +'Ajay' </p><p>Result :Ajay</p><p>SET CONCAT_NULL_YIELDS_NULL ON</p><p>Select ' ' +'Ajay'<br />Result :NULL</p><p></p><p></p><p></p><p></p><p></p>ajayhttp://www.blogger.com/profile/17516782760276199650noreply@blogger.com0