go home

sql code snippets


Find below a sql repository compiled with help from various sources on the Internet.
Most queries will be tailored for Microsoft SQL Server, as this is the relational database management system with which adqo works the most.

go to ..

  1. collate
  2. declare cursor
  3. common table expression (cte) & begin/einddatum
  4. spid query
  5. log sql server agent jobs
  6. database settings
  7. xp_cmdshell
  8. waitfor
  9. find_column
  10. find_value
  11. restore database
  12. index informatie
  13. tabel structuur
  14. foreign keys
  15. linked server
  16. serverproperty



collate


-- ############### --
-- ##  collate  ## --
-- ############### -- 
SELECT    t1.txt COLLATE SQL_Latin1_General_CP1_CI_AS AS [tekst]
FROM      tabel_1 t1,
          tabel_2 t2
WHERE     t1.txt_id = t2.txt_id COLLATE SQL_Latin1_General_CP1_CI_AS;




declare cursor


-- ###################### --
-- ##  declare cursor  ## --
-- ###################### --
DECLARE @str_xx VARCHAR(255);
DECLARE cursor_xx CURSOR FOR
        SELECT CONVERT(VARCHAR(255), GETDATE());
OPEN  cursor_xx;
FETCH NEXT FROM cursor_xx INTO @str_xx;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @str_xx;
  FETCH NEXT FROM cursor_xx INTO @str_xx;
END
CLOSE cursor_xx;
DEALLOCATE cursor_xx;




common table expression (cte) & begin/einddatum


-- ############################## --
-- ##  common table expression ## --
-- ############################## --
WITH  cte AS
     (SELECT   a
      ,        contactdatum
      ,        b
      ,        c
      FROM     tbl)
SELECT     t_begin.a                                          a
,          t_begin.contactdatum                               begindatum
,          DATEADD(SECOND,-1,MIN(t_eind.contactdatum))        einddatum
,          t_begin.b                                          b
,          t_begin.c                                          c
FROM       cte t_begin
LEFT JOIN  cte t_eind
ON         t_begin.a = t_eind.a
AND        t_begin.contactdatum < t_eind.contactdatum
GROUP BY   t_begin.a
,          t_begin.contactdatum
,          t_begin.b
,          t_begin.c




spid query


-- ################## --
-- ##  spid query  ## --
-- ################## --
SELECT       CASE WHEN statement_end_offset = -1
                    THEN text
                  ELSE SUBSTRING(text,statement_start_offset/2,(statement_end_offset-statement_start_offset)/2) END AS spid_query
,            *
FROM         sys.dm_exec_requests WITH (NOLOCK)
CROSS APPLY  sys.dm_exec_sql_text(sql_handle)
WHERE        session_id = <<spid>>;




log sql server agent jobs


-- ################################# --
-- ##  log sql server agent jobs  ## --
-- ################################# --
select       *
from         msdb.dbo.sysjobstepslogs with (nolock);




view database settings


-- ############################## --
-- ##  view database settings  ## --
-- ############################## --
SELECT       NAME                                                              AS DBNAME
,            CASE WHEN (STATUS &          1) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [AUTOCLOSE_(ALTER_DATABASE)]
,            CASE WHEN (STATUS &          4) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [SELECT_INTO/BULKCOPY_(ALTER_DATABASE_USING_SET_RECOVERY)]
,            CASE WHEN (STATUS &          8) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [TRUNC._LOG_ON_CHKPT_(ALTER_DATABASE_USING_SET_RECOVERY)]
,            CASE WHEN (STATUS &         16) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [TORN_PAGE_DETECTION_(ALTER_DATABASE)]
,            CASE WHEN (STATUS &         32) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [LOADING]
,            CASE WHEN (STATUS &         64) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [PRE_RECOVERY]
,            CASE WHEN (STATUS &        128) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [RECOVERING]
,            CASE WHEN (STATUS &        256) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [NOT_RECOVERED]
,            CASE WHEN (STATUS &        512) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [OFFLINE_(ALTER_DATABASE)]
,            CASE WHEN (STATUS &       1024) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [READ_ONLY_(ALTER_DATABASE)]
,            CASE WHEN (STATUS &       2048) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [DBO_USE_ONLY_(ALTER_DATABASE_USING_SET_RESTRICTED_USER)]
,            CASE WHEN (STATUS &       4096) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [SINGLE_USER_(ALTER_DATABASE)]
,            CASE WHEN (STATUS &      32768) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [EMERGENCY_MODE]
,            CASE WHEN (STATUS &    4194304) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [AUTOSHRINK_(ALTER_DATABASE)]
,            CASE WHEN (STATUS & 1073741824) = 0 THEN 'FALSE' ELSE 'TRUE' END  AS [CLEANLY_SHUTDOWN]
FROM         MASTER.DBO.SYSDATABASES
ORDER BY     NAME;




xp_cmdshell


-- ################### --
-- ##  xp_cmdshell  ## --
-- ################### --
EXEC sp_configure 'xp_cmdshell', 1;
  RECONFIGURE WITH OVERRIDE;

CREATE TABLE #tmp_dir
            (result VARCHAR(8000) NULL);

EXEC sp_configure 'xp_cmdshell', 1;
  RECONFIGURE WITH OVERRIDE;

INSERT INTO #tmp_dir (result)
EXEC xp_cmdshell 'DIR C:\';

EXEC sp_configure 'xp_cmdshell', 0;
  RECONFIGURE WITH OVERRIDE;

SELECT * FROM #tmp_dir;

DROP TABLE #tmp_dir;




waitfor


-- ############### --
-- ##  waitfor  ## --
-- ############### --
DECLARE   @i       INT = 0
,         @str_txt VARCHAR(50);
WHILE @i < 10
BEGIN
  SELECT @str_txt = CONVERT(VARCHAR(50),GETDATE());
  RAISERROR(@str_txt,0,1) WITH NOWAIT;
  SELECT @i=@i + 1;
  WAITFOR DELAY '00:01';
END




find column


-- ################### --
-- ##  find column  ## --
-- ################### --
SELECT     o.name  tbl,
           c.name  col
FROM       sysobjects o
INNER JOIN syscolumns c
ON         c.id = o.id
WHERE      c.name LIKE  '%abc%'
ORDER BY   1, 2;




find value


-- ################## --
-- ##  find value  ## --
-- ################## --
DECLARE @str_where_object_name_like  VARCHAR(255)
,       @str_search_text             VARCHAR(MAX)
,       @str_search_datatype         VARCHAR(255)
,       @str_sql                     VARCHAR(MAX);

SELECT  @str_where_object_name_like  = 'tbl_abc%' -- if empty ""; no search criteria
,       @str_search_datatype         = 'str'      -- "num" of "str"
,       @str_search_text             = 'xyz';
DECLARE cursor_search CURSOR FOR
        SELECT     DISTINCT
'-- [table=' + o.name + '; column=' + c.name + ']
PRINT ''-- table=[' + o.name + '] & column=[' + c.name + ']  (' + s.[name] +')'';
IF EXISTS (SELECT 1
           FROM   <<database>>.<<user>>.[' + o.name + '] WITH (NOLOCK) ' +
                   CASE WHEN @str_search_datatype = 'num'
                          THEN '
           WHERE  [' + c.name + '] = ' + @str_search_text + ') '
                        ELSE '
           WHERE  UPPER([' + c.name + ']) LIKE UPPER(''%' + @str_search_text + '%'')) '
                   END + '
BEGIN
  SELECT    TOP 50
            ''' + o.name + ''' AS [TABLE]
  ,         ''' + c.name + ''' AS [COLUMN]
  ,         [' + c.name + '] AS [VALUE  (' + o.name + ' -- ' + c.name + ')]
  ,         *
  FROM      <<database>>.<<user>>.[' + o.name + '] WITH (NOLOCK) ' +
                   CASE WHEN @str_search_datatype = 'num'
                          THEN '
  WHERE     [' + c.name + '] = ' + @str_search_text + ' '
                        ELSE '
  WHERE     UPPER([' + c.name + ']) LIKE UPPER(''%' + @str_search_text + '%'') '
                   END + '
  ORDER BY  3, 4;
END'
        FROM       <<database>>.<<user>>.sysobjects o
        LEFT JOIN  <<database>>.<<user>>.syscolumns c
        ON         o.ID = c.ID
        -- FIX .. UN-UNIQUE XTYPE --> UID = 'SYS'
        LEFT JOIN (SELECT *
                   FROM   <<database>>.<<user>>.systypes
                   INNER JOIN (SELECT T1_UID AS T4_UID
                               FROM  (SELECT   COUNT(1) AS T1_CNT
                                      ,        [UID] AS T1_UID
                                      FROM     <<database>>.<<user>>.systypes
                                      GROUP BY [UID]) T1
                               INNER JOIN(SELECT MAX(T2_CNT) AS T3_CNT
                                          FROM  (SELECT   COUNT(1) AS T2_CNT
                                                 ,        [UID] AS T2_UID
                                                 FROM     <<database>>.<<user>>.systypes
                                                 GROUP BY [UID])T2)T3
                                ON   T1_CNT = T3_CNT) T4
                   ON [UID] = T4_UID) s
        ON         c.xtype = s.xtype
        WHERE      UPPER(o.[type]) = 'U'
        AND        CASE WHEN UPPER(@str_search_datatype) = UPPER('num')
                          THEN UPPER('-INT-BIGINT-NUMERIC-FLOAT-TINYINT-DECIMAL-')
                        ELSE UPPER('-VARCHAR-CHAR-NVARCHAR-NCHAR-SYSNAME-' )
                     END LIKE UPPER('%-' + s.[name] + '-%')
        AND        UPPER(o.[name]) LIKE CASE WHEN LEN(@str_where_object_name_like) > 0
                                               THEN UPPER(@str_where_object_name_like)
                                             ELSE UPPER(o.[name]) END
        ORDER BY   1;
OPEN cursor_search;
FETCH NEXT FROM cursor_search INTO @str_sql;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC (@str_sql);
  FETCH NEXT FROM cursor_search INTO @str_sql;
END
CLOSE cursor_search;
DEALLOCATE cursor_search;






restore database


-- ############################ --
-- ##  RESTORE <<database>>  ## --
-- ############################ --
USE     master;
GO

RESTORE DATABASE <<database>>
        FROM DISK = 'C:\mssql_backup\<<database>>\<<database>>_backup.bak' WITH RECOVERY
,       MOVE '<<database>>_data' TO 'C:\mssql_data\<<database>>_data.mdf'
,       MOVE '<<database>>_log' TO 'C:\mssql_data_log\<<database>>_log.ldf';
GO

USE     <<database>>;
GO

EXEC    sp_changedbowner 'sa';
GO

ALTER   DATABASE <<database>>
MODIFY  FILE
       (NAME = <<database>>_data
,       FILEGROWTH  = 500MB);
GO

ALTER   DATABASE <<database>>
MODIFY  FILE
       (NAME = <<database>>_log
,       FILEGROWTH  = 500MB);
GO

DECLARE @str_login varchar(255);
DECLARE cursor_login CURSOR FOR SELECT name FROM sys.sysusers WHERE islogin=1 and issqluser=1 and hasdbaccess=1 and sid>0x01;
OPEN    cursor_login;
FETCH NEXT FROM cursor_login INTO @str_login;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC  sp_change_users_login 'update_one', @str_login, @str_login;
  FETCH NEXT FROM cursor_login INTO @str_login;
END
CLOSE cursor_login;
DEALLOCATE cursor_login;
GO

DECLARE @str_table varchar(255);
DECLARE cursor_table CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type='base table';
OPEN    cursor_table;
FETCH NEXT FROM cursor_table INTO @str_table;
WHILE @@FETCH_STATUS = 0
BEGIN
  DBCC DBREINDEX(@str_table,' ',90);
  FETCH NEXT FROM cursor_table INTO @str_table;
END
CLOSE cursor_table;
DEALLOCATE cursor_table;
GO

DBCC    SHRINKDATABASE (<<database>>) WITH NO_INFOMSGS;
GO

EXEC    sp_updatestats;
GO




view index informatie


-- ############################# --
-- ##  view index informatie  ## --
-- ############################# --
-->> 1) init
DECLARE      @int_value_coef  INT
,            @str_tbl         VARCHAR(100);

CREATE TABLE #tmp_index_info
            (TableName   VARCHAR(200) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
,            IndexKeys   VARCHAR(200) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
,            IsClusterd  VARCHAR(50)  COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
,            IndexName   VARCHAR(200) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
,            IndexDescr  VARCHAR(200) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS
,            IndexSize   INT );

-->> 2) retrieve index-info
DECLARE cursor_index_info CURSOR FAST_FORWARD LOCAL FOR
        SELECT    name
        FROM      <<database>>.dbo.sysobjects
        WHERE     xtype = 'u'
        ORDER BY  name;
OPEN cursor_index_info;
FETCH NEXT FROM cursor_index_info INTO @str_tbl;
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO #tmp_index_info
             (IndexName
  ,           IndexDescr
  ,           IndexKeys)
  EXEC        <<database>>.dbo.sp_helpindex
              @objname = @str_tbl;
  UPDATE      #tmp_index_info
  SET         TableName = @str_tbl
  WHERE       TableName IS NULL;
  FETCH NEXT FROM cursor_index_info INTO @str_tbl;
END
CLOSE cursor_index_info;
DEALLOCATE cursor_index_info;

-->> 3) enrich index-info
SELECT       @int_value_coef = LOW
FROM         master.dbo.spt_values
WHERE        number = 1
AND          [type] = N'E';

UPDATE       #tmp_index_info
SET          IndexSize = ((CONVERT(BIGINT, i.USED) * @int_value_coef)/1024)/1024
FROM         <<database>>.dbo.sysobjects o
INNER JOIN   <<database>>.dbo.sysindexes i
ON           o.id = i.id
INNER JOIN   #tmp_index_info t
ON           t.TableName = o.name
AND          t.IndexName = i.name;

UPDATE       #tmp_index_info
SET          IsClusterd = CASE WHEN CHARINDEX('NONCLUSTERED',IndexDescr ) > 0 THEN 'N'
                               WHEN CHARINDEX('CLUSTERED',IndexDescr ) > 0 THEN 'Y'
                               ELSE '?' END;

-->> 4) show result
SELECT       *
FROM         #tmp_index_info
ORDER BY     TableName
,            IndexName;

-->> 5) clean-up
DROP TABLE   #tmp_index_info;




tabel structuur


-- ####################### --
-- ##  tabel structuur  ## --
-- ####################### --
DECLARE      @str_tbl VARCHAR(255)='<<table>>'; 
SELECT       ISNULL(CONVERT(VARCHAR(12), pk_keyno),'')                        AS pk 
,            o.name                                                            AS tbl 
,            c.colorder                                                        AS col_nr 
,            c.name                                                            AS col_nm                 
,            CASE WHEN s.name  IN('numeric','decimal') 
                  THEN s.name  +'('+ CONVERT(VARCHAR(12), c.xprec)+', ' 
                              +CONVERT(VARCHAR(12), c.xscale)+')' 
                  WHEN s.name  IN('varchar','char', 'nvarchar','nchar','sysname') 
                  THEN s.name  +'('+ CONVERT(VARCHAR(12), c.length)+')' 
                  WHEN s.name  IN('datetime','text') 
                  THEN s.name 
                  ELSE s.name  END                                               AS datatype 
,            CASE WHEN c.isnullable = 0 
                  THEN 'NOT NULL' 
                  ELSE 'NULL' END                                               AS isnullable 
,            s.name                                                            AS [type] 
,            c.[length]                                                        AS [length] 
,            c.xprec                                                           AS [xprec] 
,            c.xscale                                                          AS xscale 
FROM         sysobjects o 
LEFT JOIN    syscolumns c 
ON           o.id = c.id 
-- fix .. un-unique xtype --> uid = 'sys' 
LEFT JOIN   (SELECT* 
             FROM   systypes 
             ,     (SELECT t1_uid AS t4_uid 
                    FROM  (SELECT COUNT(1) AS t1_cnt 
                           ,      [uid] AS t1_uid 
                           FROM   systypes 
                           GROUP  BY [uid]) t1 
                    ,     (SELECT MAX(t2_cnt) AS t3_cnt 
                           FROM  (SELECT COUNT(1) AS t2_cnt 
                                  ,      [uid] AS t2_uid 
                                  FROM   systypes 
                                  GROUP  BY [uid])t2)t3 
                           WHERE   t1_cnt = t3_cnt) t4 
                    WHERE  [uid] = t4_uid) s 
ON           c.xtype = s.xtype 
LEFT JOIN   (SELECT      o.name AS pk_object 
             ,           ik.keyno AS pk_keyno 
             ,           c.name AS pk_column 
             FROM        sysindexes i 
             INNER JOIN  sysobjects o 
             ON          i.id  = o.id 
             INNER JOIN  sysobjects pk 
             ON          i.name = pk.name 
             AND         pk.parent_obj = i.id 
             AND         pk.xtype ='pk' 
             INNER JOIN  sysindexkeys ik 
             ON          i.id  = ik.id 
             AND         i.indid = ik.indid 
             INNER JOIN  syscolumns c 
             ON          ik.id = c.id 
             AND         ik.colid = c.colid) pk 
ON           o.name = pk_object 
AND          c.name = pk_column 
WHERE        o.name LIKE @str_tbl 
AND          o.[type] ='u' 
ORDER BY     o.name 
,            CASE WHEN ISNULL(CONVERT(VARCHAR(12), pk_keyno),'') ='' 
                    THEN'z' 
                  ELSE ISNULL(CONVERT(VARCHAR(12), pk_keyno),'') END 
,            c.colorder; 




foreign keys


-- #################### --
-- ##  foreign keys  ## --
-- #################### --
SELECT  f.name AS ForeignKey,
        OBJECT_NAME(f.parent_object_id) AS TableName,
        COL_NAME(f_col.parent_object_id,
        f_col.parent_column_id) AS ColumnName,
        OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
        COL_NAME(f_col.referenced_object_id,
        f_col.referenced_column_id) AS ReferenceColumnName
FROM    sys.foreign_keys AS f
         INNER JOIN sys.foreign_key_columns AS f_col
         ON         f.OBJECT_ID = f_col.constraint_object_id

  --or--

SELECT CONVERT(VARCHAR(255), o_fk.name) AS foreign_key_name,
       r.keycnt,
       CONVERT(VARCHAR(255), o_tp.name) AS primary_table,
       CONVERT(VARCHAR(255), o_tf.name) AS foreign_table,
       CONVERT(VARCHAR(255), c_tp1.name) AS primary_column_1,
       CONVERT(VARCHAR(255), c_tf1.name) AS foreign_column_1,
       CONVERT(VARCHAR(255), c_tp2.name) AS primary_column_2,
       CONVERT(VARCHAR(255), c_tf2.name) AS foreign_column_2,
       CONVERT(VARCHAR(255), c_tp3.name) AS primary_column_3,
       CONVERT(VARCHAR(255), c_tf3.name) AS foreign_column_3,
       CONVERT(VARCHAR(255), c_tp4.name) AS primary_column_4,
       CONVERT(VARCHAR(255), c_tf4.name) AS foreign_column_4,
       CONVERT(VARCHAR(255), c_tp5.name) AS primary_column_5,
       CONVERT(VARCHAR(255), c_tf5.name) AS foreign_column_5,
       CONVERT(VARCHAR(255), c_tp6.name) AS primary_column_6,
       CONVERT(VARCHAR(255), c_tf6.name) AS foreign_column_6
FROM   sysobjects o_fk
         INNER JOIN sysobjects o_tf
         ON    o_fk.parent_obj = o_tf.id
           INNER JOIN sysreferences r
           ON    o_fk.id =  r.constid
             INNER JOIN sysobjects o_tp
             ON    r.rkeyid = o_tp.id

               -- 1 --
               INNER JOIN syscolumns c_tp1
               ON    r.rkeyid = c_tp1.id
               AND   r.rkey1 = c_tp1.colid
                 INNER JOIN syscolumns c_tf1
                 ON    r.fkeyid = c_tf1.id
                 AND   r.fkey1 = c_tf1.colid

               -- 2 --
               LEFT JOIN syscolumns c_tp2
               ON    r.rkeyid = c_tp2.id
               AND   r.rkey2 = c_tp2.colid
                 LEFT JOIN syscolumns c_tf2
                 ON    r.fkeyid = c_tf2.id
                 AND   r.fkey2 = c_tf2.colid
 
              -- 3 --
               LEFT JOIN syscolumns c_tp3
               ON    r.rkeyid = c_tp3.id
               AND   r.rkey3 = c_tp3.colid
                 LEFT JOIN syscolumns c_tf3
                 ON    r.fkeyid = c_tf3.id
                 AND   r.fkey3 = c_tf3.colid

               -- 4 --
               LEFT JOIN syscolumns c_tp4
               ON    r.rkeyid = c_tp4.id
               AND   r.rkey4 = c_tp4.colid
                 LEFT JOIN syscolumns c_tf4
                 ON    r.fkeyid = c_tf4.id
                 AND   r.fkey4 = c_tf4.colid

               -- 5 --
               LEFT JOIN syscolumns c_tp5
               ON    r.rkeyid = c_tp5.id
               AND   r.rkey5 = c_tp5.colid
                 LEFT JOIN syscolumns c_tf5
                 ON    r.fkeyid = c_tf5.id
                 AND   r.fkey5 = c_tf5.colid

               -- 6 --
               LEFT JOIN syscolumns c_tp6
               ON    r.rkeyid = c_tp6.id
               AND   r.rkey6 = c_tp6.colid
                 LEFT JOIN syscolumns c_tf6
                 ON    r.fkeyid = c_tf6.id
                 AND   r.fkey6 = c_tf6.colid

WHERE  o_fk.type =  'F'
ORDER BY 3, 4, 5;




linked server


-- ##################### --
-- ##  linked server  ## --
-- ##################### --
EXEC sp_addlinkedserver   @server='LOCALSERVER'
,                         @srvproduct=''
,                         @provider='SQLOLEDB'
,                         @datasrc=<<servername>>;

EXEC sp_addlinkedsrvlogin @rmtsrvname='srvname'
,                         @rmtuser='user'
,                         @rmtpassword='password';

CREATE TABLE #tmp_tbl (tbl VARCHAR(8000));

INSERT INTO  #tmp_tbl (tbl)
SELECT       *
FROM         OPENQUERY((<<linked_server>>, 'show tables');

SELECT       *
INTO         <<database>>.dbo.table1
FROM         OPENQUERY(<<linked_server>>, 'SELECT * FROM table1);




serverproperty


-- ###################### --
-- ##  serverproperty  ## --
-- ###################### --
SELECT  SERVERPROPERTY('BuildClrVersion')                AS BuildClrVersion
,       SERVERPROPERTY('Collation')                      AS Collation
,       SERVERPROPERTY('CollationID')                    AS CollationID
,       SERVERPROPERTY('ComparisonStyle')                AS ComparisonStyle
,       SERVERPROPERTY('ComputerNamePhysicalNetBIOS')    AS ComputerNamePhysicalNetBIOS
,       SERVERPROPERTY('Edition')                        AS Edition
,       SERVERPROPERTY('EditionID')                      AS EditionID
,       SERVERPROPERTY('EngineEdition')                  AS EngineEdition
,       SERVERPROPERTY('FilestreamConfiguredLevel')      AS FilestreamConfiguredLevel
,       SERVERPROPERTY('FilestreamEffectiveLevel')       AS FilestreamEffectiveLevel
,       SERVERPROPERTY('FilestreamShareName')            AS FilestreamShareName
,       SERVERPROPERTY('InstanceName')                   AS InstanceName
,       SERVERPROPERTY('IsClustered')                    AS IsClustered
,       SERVERPROPERTY('IsFullTextInstalled')            AS IsFullTextInstalled
,       SERVERPROPERTY('IsIntegratedSecurityOnly')       AS IsIntegratedSecurityOnly
,       SERVERPROPERTY('IsSingleUser')                   AS IsSingleUser
,       SERVERPROPERTY('LCID')                           AS LCID
,       SERVERPROPERTY('LicenseType')                    AS LicenseType
,       SERVERPROPERTY('MachineName')                    AS MachineName
,       SERVERPROPERTY('NumLicenses')                    AS NumLicenses
,       SERVERPROPERTY('ProcessID')                      AS ProcessID
,       SERVERPROPERTY('ProductLevel')                   AS ProductLevel
,       SERVERPROPERTY('ProductVersion')                 AS ProductVersion
,       SERVERPROPERTY('ResourceLastUpdateDateTime')     AS ResourceLastUpdateDateTime
,       SERVERPROPERTY('ResourceVersion')                AS ResourceVersion
,       SERVERPROPERTY('ServerName')                     AS ServerName
,       SERVERPROPERTY('SqlCharSet')                     AS SqlCharSet
,       SERVERPROPERTY('SqlCharSetName')                 AS SqlCharSetName
,       SERVERPROPERTY('SqlSortOrder')                   AS SqlSortOrder
,       SERVERPROPERTY('SqlSortOrderName')               AS SqlSortOrderName;