Techie's Diary

It's one of the common question while deleting column from a table. How we will delete a column having default constraint. If we try to delete a column having Default constraint, then it will raise error.  Looking for Answer, here we go: Declare two variables as: DECLARE  @ConstraintName  NVARCHAR (200), @sql    NVARCHAR (MAX) Now set the value of @ConstraintName as Default constraint name: SET  @ConstraintName = ( SELECT  NAME  AS  DEFAULT_CONSTRAINT_NAME FROM  SYS.DEFAULT_CONSTRAINTS WHERE  OBJECT_NAME (PARENT_OBJECT_ID) =  '<Table_Name>' ) After this create run-time query to delete constraint and set it to @sql: SET  @sql =  N'ALTER TABLE <Table_Name> DROP CONSTRAINT '  + @ConstraintName And with the help of sp_executesql drop the constraint as: EXEC  sp_executesql  @sql And as constraint is deleted so finally delete the column without any error. ALTER TABLE  <Table_Name>  DROP COLUMN  <Co

It's common problem which we face while working with SQL Server, sometimes we remembers the object name (fully or partially), but wanted to know in which DB that object occured, or sometime we want how many occurrence exists for that particular object in all databases. So stuck with this...here is solution: --------FOR SQL SERVER 2005 or more --------- CREATE TABLE #TEMP ( DATABASENAME   SYSNAME , OBJECTNAME SYSNAME , TYPE CHAR (10)) INSERT INTO #TEMP EXEC SP_MSFOREACHDB "SELECT '?' DATABASENAME, NAME,TYPE FROM ?.SYS.ALL_OBJECTS  WHERE NAME = 'SearchText'" SELECT * FROM #TEMP DROP TABLE #TEMP Pass your object name in place of SearchText , above query will show all the object with such name, but what if we know only a part of object name...in such cases SQL Server's Like operator will be handy. We can use modify Where Clause as: EXEC  SP_MSFOREACHDB  "SELECT '?' DATABASENAME, NAME,TYPE FROM ?.SYS.ALL_OBJECTS  WHERE  Like

Datatable is used since the invention of ADO.Net and this object is very useful to carry data all along the application. As development phase goes forward, LINQ come in picture..now a days LINQ is used in almost every second application, but sometime we have requirement that we have data in LINQ but want it in datatable, we can easily convert  from LINQ to DT with the help of reflection as:                   /// <summary>         /// Convert LINQ Output to Datatable.         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="varlist"></param>         /// <returns></returns>         private DataTable ConvertLINQToDataTable<T>(IEnumerable<T> varlist)         {             DataTable dtTest = new DataTable();             // column names              System.Reflection.PropertyInfo[] oProps = null;             if (varlist == null) return dtTest;                fo

Sometime we need to format date as per our need,. for e.g. sometimes we want to show date in form of   JAN 05, 2012 etc. We can format date either in SQL server or we can format the date in code too. Here I will show you how we can change the date in SQL server based on a specific date format. first of all we will create function as: CREATE FUNCTION dbo.funFormatDate (@Datetime DATETIME, @DateFormat VARCHAR(32)) RETURNS VARCHAR(50) AS BEGIN     DECLARE @StringDate VARCHAR(32)     SET @StringDate = @DateFormat     IF (CHARINDEX ('YYYY',@StringDate) > 0)        SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))     IF (CHARINDEX ('YY',@StringDate) > 0)        SET @StringDate = REPLACE(@StringDate, 'YY',RIGHT(DATENAME(YY, @Datetime),2))     IF (CHARINDEX ('Month',@StringDate) > 0)        SET @StringDate = REPLACE(@StringDate, 'Month',DATENAME(MM, @Datetime))     IF (CHARINDEX ('MON