-- TSQL Search for specified word in Stored Procedures

-- TSQL Search for specified word in Stored Procedures

 SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'TextToSearch' + '%'
AND TYPE = 'P'


--tsql find column around database tables

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%DelFlag%'
ORDER BY schema_name, table_name;

Using sp_executesql

MSDN :

To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.

Self-contained Batches

---

When either sp_executesql or the EXECUTE statement executes a string, the string is executed as its own self-contained batch. SQL Server compiles the Transact-SQL statement or statements in the string into an execution plan that is separate from the execution plan of the batch that contained the sp_executesql or the EXECUTE statement. The following rules apply for self-contained batches:

• The Transact-SQL statements in the sp_executesql or EXECUTE string are not compiled into an execution plan until sp_executesql or the EXECUTE statement are executed. The strings are not parsed or checked for errors until they are executed. The names referenced in the strings are not resolved until they are executed.
• The Transact-SQL statements in the executed string do not have access to any variables declared in the batch that contains sp_executesql or the EXECUTE statement. The batch that contains sp_executesql or the EXECUTE statement does not have access to variables or local cursors defined in the executed string.
• If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running.

Running the following two batches illustrates these points:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Substituting Parameter Values

---

sp_executesql supports the substitution of parameter values for any parameters that are specified in the Transact-SQL string, but the EXECUTE statement does not. Therefore, the Transact-SQL strings that are generated by sp_executesql are more similar than those generated by the EXECUTE statement. The SQL Server query optimizer will probably match the Transact-SQL statements from sp_executesql with execution plans from the previously executed statements, saving the overhead of compiling a new execution plan.

With the EXECUTE statement, all parameter values must be converted to character or Unicode and made a part of the Transact-SQL string.

If the statement is executed repeatedly, a completely new Transact-SQL string must be built for each execution, even when the only differences are in the values supplied for the parameters. This generates additional overhead in the following ways:

• The ability of the SQL Server query optimizer to match the new Transact-SQL string with an existing execution plan is hampered by the constantly changing parameter values in the text of the string, especially in complex Transact-SQL statements.
• The whole string must be rebuilt for each execution.
• Parameter values (other than character or Unicode values) must be cast to a character or Unicode format for each execution.

sp_executesql supports setting of parameter values separately from the Transact-SQL string:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql offers the following additional benefits:

• Because the actual text of the Transact-SQL statement does not change between executions, the query optimizer should match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement.
• The Transact-SQL string is built only one time.
• The integer parameter is specified in its native format. Conversion to Unicode is not required.
  

  Note
  Object names in the statement string must be fully qualified for SQL Server to reuse the execution plan.

Reusing Execution Plans

---

In earlier versions of SQL Server, the only way to be able to reuse execution plans is to define the Transact-SQL statements as a stored procedure and have the application execute the stored procedure. This generates additional administrative overhead for the applications. Using sp_executesql can help reduce this overhead and still let SQL Server reuse execution plans. sp_executesql can be used instead of stored procedures when executing a Transact-SQL statement several times, when the only variation is in the parameter values supplied to the Transact-SQL statement. Because the Transact-SQL statements themselves remain constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

The following example builds and executes a DBCC CHECKDB statement for every database on a server, except for the four system databases.

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;

DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);

FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar;
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
   EXEC sp_executesql @Statement;
   PRINT CHAR(13) + CHAR(13);
   FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;

CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO

The SQL Server ODBC driver uses sp_executesql to implement SQLExecDirect when the Transact-SQL statement that is being executed contains bound parameter markers. This extends the advantages provided bysp_executesql to all applications that use ODBC or APIs defined over ODBC, such as RDO. Existing ODBC applications ported to SQL Server automatically acquire the performance gains without having to be rewritten. The one exception is that sp_executesql is not used with data-at-execution parameters. For more information, see Using Statement Parameters.

The SQL Server Native Client ODBC Provider also uses sp_executesql to implement the direct execution of statements with bound parameters. Applications that use OLE DB or ADO gain the advantages provided bysp_executesql without having to be rewritten.

http://msdn.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

TSql Manipulating XML (XQUERY)

--TSQL SELECT FROM XML FIELD

DECLARE @xmlData XML
set @xmlData='<?xml version="1.0"?>
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ITEM>
<FilterID>1219</FilterID>
<Name>Fred</Name>
<Code>510</Code>
<Department>N</Department>
<Number>305327</Number>
</ITEM>
<ITEM>
<FilterID>3578</FilterID>
<Name>Gary</Name>
<Code>001</Code>
<Department>B</Department>
<Number>0692690</Number>
</ITEM>
<ITEM>
<FilterID>3579</FilterID>
<Name>George</Name>
<Code>001</Code>
<Department>X</Department>
<Number>35933</Number>
</ITEM>
</ROOT>'


SELECT
  ref.value('FilterID[1]', 'int') AS FilterID ,
  ref.value('Name[1]', 'NVARCHAR (10)') AS Name ,
  ref.value('Code[1]', 'NVARCHAR (10)') AS Code ,
  ref.value('Department[1]', 'NVARCHAR (3)') AS Department,
  ref.value('Number[1]', 'int') AS Number    
FROM @xmlData.nodes('/ROOT/ITEM')
xmlData( ref )

--TSQL SELECT TO XML FIELD

select * from dbo.TBLFOO
        FOR XML PATH('ITEM'), root('ROOT')

SQL SERVER GET FOREIGN CONSTRAINTS RELATTIONS

--HOW TO GET FOREIGN CONSTRAINTS RELATED TO THIS TABLE

 SELECT
        FK_Table  = FK.TABLE_NAME,
        FK_Column = CU.COLUMN_NAME,
        PK_Table  = PK.TABLE_NAME,
        PK_Column = PT.COLUMN_NAME,
        Constraint_Name = C.CONSTRAINT_NAME
    FROM
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
        INNER JOIN
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
            ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
        INNER JOIN
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
            ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
        INNER JOIN
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
            ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
        INNER JOIN
        (
            SELECT
                i1.TABLE_NAME, i2.COLUMN_NAME
            FROM
                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                INNER JOIN
                INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) PT
        ON PT.TABLE_NAME = PK.TABLE_NAME

    WHERE PK.TABLE_NAME='TABLE_NAME'

--HOW TO GET FOREIGN CONSTRAINTS USED IN THIS TABLE


SELECT
    c.name
    , target.name
    , targetc.name
FROM
    -- source table
    sysobjects t
    -- source column
    INNER JOIN syscolumns c ON t.id = c.id
    -- general constraint
    INNER JOIN sysconstraints co ON t.id = co.id AND co.colid = c.colid
    -- foreign key constraint
    INNER JOIN sysforeignkeys fk ON co.constid = fk.constid
    -- target table
    INNER JOIN sysobjects target ON fk.rkeyid = target.id
    -- target column
    INNER JOIN syscolumns targetc ON fk.rkey = targetc.colid AND fk.rkeyid = targetc.id
WHERE
    t.name = 'TABLE_NAME'

--OR

exec sp_fkeys @fktable_name = 'TABLE_NAME'

c# difference var and dynamic keyword

var is static typed - the compiler and runtime know the type - they just save you some typing... the following are 100% identical:

var s = "abc";
Console.WriteLine(s.Length);

and

string s = "abc";
Console.WriteLine(s.Length);

All that happened was that the compiler figured out that s must be a string (from the initializer). In both cases, it knows (in the IL) that s.Length means the (instance) string.Length property.
dynamic is a very different beast; it is most similar to object, but with dynamic dispatch:

dynamic s = "abc";
Console.WriteLine(s.Length);

Here, s is typed as dynamic. It doesn't know about string.Length, because it doesn't know anything about s at compile time. For example, the following would compile (but not run) too:

dynamic s = "abc";
Console.WriteLine(s.FlibbleBananaSnowball);

At runtime (only), it would check for the FlibbleBananaSnowball property - fail to find it, and explode in a shower of sparks.
With dynamic, properties / methods / operators / etc are resolved at runtime, based on the actual object. Very handy for talking to COM (which can have runtime-only properties), the DLR, or other dynamic systems, like javascript.
.

Variables declared with var are implicitly but statically typed. Variables declared with dynamic are dynamically typed. This capability was added to the CLR in order to support dynamic languages like Ruby and Python.
I should add that this means that dynamic declarations are resolved at run-time, var declarations are resolved at compile-time.
.
http://stackoverflow.com/questions/961581/whats-the-difference-between-dynamicc-4-and-var