Skip to main content

Posts

Showing posts from January, 2012

Delete column having default constraint in SQL server

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 <Column_Name>
Here is complete script for copy paste champs..;)
DECLARE @ConstraintName NVARC…

Search for an object in all the databases in SQL Server

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 (DATABASENAMESYSNAME, 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 '%SearchText%'"

And it will …

Convert LINQ to Datatable

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;
            foreach (T rec in varlist)
            {
             …

Format date with SQL Server function in different format

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',@StringDate COLLATE SQL_Latin1_Gene…