.Net Pickles

In this tutorial i am going to explain about sql script or query to get the columns of a particular table or all the tables in the database along with datatype and default values of that particular column like null,not null,empty or getdate() etc..

In this article i am going to explain about how to drop multiple tables,procedures or functions at once. Sometimes we supposed to drop multiple tables. For explanation purpose i have created two tables namely testtable1 and testtable2 and i have created two procedures namely GetTestTable1 and GetTestTable2. Now i will explain the syntax and the query used using this two tables and procedures. Below is the sql script used to create the sample.

Introduction : In my previous article I have explained about SQL Query to backup database in sql server , SQL query to extract file names from a html content , How to set deadlock priority in sql server , Find count of each characters in a string in SQL Server , Use of SET NOCOUNT in sql server , Different Isolation levels in sql server in SQL Server . In this article I am going to explain about how to create cursor in sql server with example in sql server 2005/2008. Explanation: A cursor is a set of rows together with a pointer that identifies a current row. In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, it’s like recordset in the ASP and visual basic. Syntax: DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]      [ FORWARD_ONLY | SCROLL ]      [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]      [ READ_ONLY | SCROLL_LOCKS | OPTIMISTI...

In this article i am going to explain about how to create table in sql server 2008. "Create Table" Statement is uesd to create table in sql server. In sql server tables are organised as rows and columns.And each column will have name and the datatype it accepts. So creating table in sql server we need to create columns with datatype. Below is the syntax to create table in sql server 2008. CREATE TABLE table_name ( column_name1 data_type ( size ), column_name2 data_type ( size ), column_name3 data_type ( size ), .... ); Below is the script to create student table CREATE TABLE StudentMaster ( Name VARCHAR ( 50 ), Standard INT , Section CHAR ( 5 ) ) After creating table you can check it in the object explorer. After creating table we can insert the record in the table in the below format INSERT INTO StudentMaster ( Name , Standard , Section ) VALUES ( 'Kannadasan' , '12' , 'A' ) ...

I was asked a question or a series of questions, "How would you do paging in a SQL? How would you do this with a lot of records, say, 10,000 or more?" I thought about answers. To be more precise, I thought of more questions and this got me thinking, "This must be a common problem, every developer must have done or solved this. What about paging sizes and working with very large data sets? What about getting results from multiple tables?" So, I decided to look into these questions and found the queries that works in different versions of sql server Pagination in sql server 2000 declare @pageNumber int , @pageSize int set @pageNumber = 2 set @pageSize = 10 SELECT * FROM (     SELECT TOP ( @pageSize ) * FROM     (         SELECT TOP ( @pageNumber * @pageSize ) *         FROM Citymaster         ORDER BY CityID AS...

In this article i am going to expalin the query used to fetch the inbetween records from a table For example if my requirement is to fetch from 30th to 50th record from citymaster table then i can use the below query. We can achieve it using OFFSET FETCH clause.

The below three built-in functions can be used to get the identity of the mostly added record. @IDENTITY It returns the last identity value generated for any table in the current session, across all scopes. Let me explain this... suppose we create an  insert  trigger on table which inserts a row in another table with generate an identity column, then  @@IDENTITY  returns that identity record which is created by trigger.

To find the previous date in SQL Server below query is used SELECT * FROM myTable WHERE C rDate < DATEADD ( d , - 1 , GetDate ()) The DATEADD ( d , - 1 , GetDate ()) function will subtract one from the current date and return the results In C#.Net we can do it using the below line of code DateTime .Now.AddDays(-1)

Below is the query used to find the modified tables,functions and stored procedures in last 1 day. SELECT   *   FROM   sys . objects WHERE   DATEDIFF ( D , modify_date ,   GETDATE ())   <  1 To find only modified functions in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'fn' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1 To find only modified tables in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'U' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1 To find only modified procedures in last one day below query is used SELECT * FROM sys . objects WHERE TYPE = 'P' AND DATEDIFF ( D , modify_date , GETDATE ()) <  1

In this article i am going to explain about the query used to to check uppercase letter is present in the string. Below is the query which i used to do this operation. It will return 1 if the upper case letter is there otherwise returns 0. DECLARE       @TestString VARCHAR ( 100 ) SET @TestString = 'Sarat' SELECT CASE WHEN BINARY_CHECKSUM ( @TestString ) = BINARY_CHECKSUM ( LOWER ( @TestString )) THEN 0 ELSE 1 END AS DoesContainUpperCase GO