/* Find Collation of SQL Server Database */

SELECT DATABASEPROPERTYEX('DBName', 'Collation')

GO

/* Find Collation of SQL Server Database Table Column */

USE sample

GO

SELECT name, collation_name

FROM sys.columns

WHERE OBJECT_ID IN (

SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'TableName')

AND name = 'ColumnName'

SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order Details]

where OrderID = '10248'

FOR XML PATH('')

SELECT ',' + ltpid

FROM V_ltp_main WHERE ltpkind = 2

FOR XML PATH('')

SELECT STUFF('abcdef', 2, 3, 'ijklmn')

select STUFF(

(SELECT ',' + ltpid FROM V_ltp_main WHERE ltpkind = 2 FOR XML PATH(''))

, 1, 1, '')

提供者選項中 'compilerVersion' 屬性的值必須是 'v4.0' 以上 (如果要針對 4.0 版以後的 .NET Framework 編譯)。若要針對 3.5 (含) 以後版本的 .NET Framework 編譯這個 Web 應用程式，請從 Web.config 檔案的 <compilation> 項目移除 'targetFramework' 屬性。

<system.codedom>

    <compilers>

     <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CSharp.CSharpCodeProvider,System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4">

        <providerOption name="CompilerVersion" value="v4.0"/>

        <providerOption name="WarnAsError" value="false"/>

     </compiler>

     <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" warningLevel="4">

        <providerOption name="CompilerVersion" value="v4.0"/>

        <providerOption name="OptionInfer" value="true"/>

        <providerOption name="WarnAsError" value="false"/>

     </compiler>

    </compilers>

</system.codedom>

<system.web>

    <compilation targetFramework="4.0" />

</system.web>

SELECT

    DB_NAME(dbid) as DBName,

    COUNT(dbid) as NumberOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE

    dbid > 0

GROUP BY

    dbid, loginame

USE [master]

GO

/****** Object: StoredProcedure [dbo].[sp_who3]    Script Date: 06/26/2008 09:40:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[sp_who3]    

(    

@filter tinyint = 1,    

@filterspid int = NULL    

)    

AS    

SET NOCOUNT ON;    

    

DECLARE @processes TABLE    

(    

spid int,    

blocked int,    

databasename varchar(256),    

hostname varchar(256),    

program_name varchar(256),    

loginame varchar(256),    

status varchar(60),    

cmd varchar(128),    

cpu int,    

physical_io int,    

[memusage] int,    

login_time datetime,    

last_batch datetime,    

current_statement_parent xml,

current_statement_sub xml)



INSERT INTO @processes    

SELECT sub.*

FROM

(

SELECT sp.spid,    

sp.blocked,    

sd.name,    

RTRIM(sp.hostname) AS hostname,

RTRIM(sp.[program_name]) AS [program_name],    

RTRIM(sp.loginame) AS loginame,    

RTRIM(sp.status) AS status,    

sp.cmd,    

sp.cpu,    

sp.physical_io,    

sp.memusage,    

sp.login_time,    

sp.last_batch,

(

    SELECT

LTRIM(st.text) AS [text()]

    FOR XML PATH(''), TYPE

) AS parent_text,

(

    SELECT

LTRIM(CASE

WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL

ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,

((CASE er.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE er.statement_end_offset

END - er.statement_start_offset)/2) + 1)

END) AS [text()]

FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

WHERE er.session_id = sp.spid

    FOR XML PATH(''), TYPE

) AS child_text

FROM sys.sysprocesses sp WITH (NOLOCK) LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid

CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st

) sub INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid

ORDER BY

sub.spid



-- if specific spid required    

IF @filterspid IS NOT NULL    

DELETE @processes    

WHERE spid <> @filterspid    

    

-- remove system processes    

IF @filter = 1 OR @filter = 2    

DELETE @processes    

WHERE spid < 51

OR spid = @@SPID

    

-- remove inactive processes    

IF @filter = 2    

DELETE @processes    

WHERE status = 'sleeping'    

AND cmd IN ('AWAITING COMMAND')    

AND blocked = 0    



SELECT spid,    

blocked,    

databasename,    

hostname,    

loginame,    

status,    

current_statement_parent,

current_statement_sub,

cmd,    

cpu,    

physical_io,    

program_name,    

login_time,    

last_batch    

FROM @processes    

ORDER BY spid



RETURN 0; 

DECLARE @Table TABLE(

        spid int,    

        blocked int,    

        databasename varchar(256),    

        hostname varchar(256),    

        loginame varchar(256),    

        status varchar(60),    

        current_statement_parent xml,

        current_statement_sub xml,

        cmd varchar(128),    

        cpu int,    

        physical_io int,    

        program_name varchar(256),    

        login_time datetime,    

        last_batch datetime

)



INSERT INTO @Table EXEC sp_who3



SELECT *

FROM    @Table

WHERE databasename= 'Leon' and cmd = 'AWAITING COMMAND'

and CAST(current_statement_parent AS nvarchar(max)) not LIKE '%AspNet_SqlCachePollingStoredProcedure%'

order by CAST(current_statement_parent AS nvarchar(max))