Microstrategy Check for Users

/***************************************************************************

MSTR CHECK USER

*****************************************************************************/

select g.OBJECT_NAME as Groups, u.OBJECT_NAME as Users, u.ABBREVIATION, u.DESCRIPTION

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableNew";'

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser";'

     , 'REMOVE USER "'+ cast(u.ABBREVIATION as varchar) +'" FROM GROUP "_DisableNew";'

 from DSSMDOBJDEPN d

 join DSSMDOBJINFO u

   on d.OBJECT_ID = u.OBJECT_ID

 join DSSMDOBJINFO g

   on d.DEPN_OBJID = g.OBJECT_ID

where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

  and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

  and g.OBJECT_NAME LIKE 'MSTRProjectName%'--IN ('_Enable')

  and u.ABBREVIATION NOT IN (

select u.ABBREVIATION

  from DSSMDOBJDEPN d

  join DSSMDOBJINFO u

    on d.OBJECT_ID = u.OBJECT_ID

  join DSSMDOBJINFO g

    on d.DEPN_OBJID = g.OBJECT_ID

 where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

   and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

   and g.OBJECT_NAME IN ('_Enable'))

   AND u.ABBREVIATION NOT IN

     ( select u.ABBREVIATION

         from DSSMDOBJDEPN d

         join DSSMDOBJINFO u

           on d.OBJECT_ID = u.OBJECT_ID

         join DSSMDOBJINFO g

           on d.DEPN_OBJID = g.OBJECT_ID

        where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

          and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

          and g.OBJECT_NAME NOT LIKE 'MSTRProjectName%'

          and g.OBJECT_NAME NOT IN ('Everyone') 

      )

order by 4

-- ALTER USERS IN USER GROUP ' _DisableUser1' DISABLED;

select g.OBJECT_NAME as Groups, u.OBJECT_NAME as Users, u.ABBREVIATION, u.DESCRIPTION

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser1";'

     , 'ADD USER "' + cast(u.ABBREVIATION as varchar)  +'" TO GROUP "_DisableUser";'

     , 'REMOVE USER "'+ cast(u.ABBREVIATION as varchar) +'" FROM GROUP "_DisableUser1";'

  from DSSMDOBJDEPN d

  join DSSMDOBJINFO u

    on d.OBJECT_ID = u.OBJECT_ID

  join DSSMDOBJINFO g

    on d.DEPN_OBJID = g.OBJECT_ID

 where u.OBJECT_TYPE = 34 and u.SUBTYPE = 8704 --Users

   and g.OBJECT_TYPE = 34 and g.SUBTYPE = 8705 --Groups

   and g.OBJECT_NAME NOT IN ('_DisableUser1')

   and g.OBJECT_NAME IN ('Everyone')

   and u.ABBREVIATION in ( 'Abbreviation')

   and u.DESCRIPTION not in ('Description')

order by 4

-- ALTER USERS IN USER GROUP ' _DisableUser1' DISABLED;

Add a TRIM for LTRIM/RTRIM

LTRIM() & RTRIM() (and always having to call them together), and of not catching 'dirty' data that had newlines at the

--end, so I decided it was high time to implement a better TRIM function. I'd welcome peer feedback!

--Disclaimer: this actually removes (replaces with a single white space) extended forms of white space (tab, line-feed, carriage-return, etc.), 

--The idea here is that your string doesn't need such extra special-white space characters inside it, and so

--if they don't occur at the head/tail, they should be replaced with a plain space. If you purposefully stored

--such characters in your string (say, your column of data that you're about to run this on), 

--DON'T DO IT! Improve this function or write your own that literally just removes those characters

--from the endpoints of the string, not from the 'body'.

--Okay, now that the disclaimer is updated, here's the code.

-- =============================================

-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,

-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab,

-- form-feed, & carriage-return (respectively), with a whitespace

-- (and then trims that off if it's still at the beginning or end, of course).

-- =============================================

CREATE FUNCTION [fn_CleanTrim] (

       @Str nvarchar(max)

)

RETURNS nvarchar(max) AS

BEGIN

       DECLARE @Result nvarchar(max)

       SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

              LTRIM(RTRIM(@Str)), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' ')))

       RETURN @Result

END

IF OBJECT_ID('dbo.fn_CleanAndTrim') IS NULL

    EXEC ('CREATE FUNCTION dbo.fn_CleanTrim () RETURNS INT AS BEGIN RETURN 0 END')

GO

-- =============================================

-- Author: Nate Johnson

-- Source: http://stackoverflow.com/posts/24068265

-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,

-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab, form-feed,

-- & carriage-return (respectively), with a whitespace or specified character(s).

-- Option "@PurgeReplaceCharsAtEnds" determines whether or not to remove extra head/tail

-- replacement-chars from the string after doing the initial replacements.

-- This is only truly useful if you're replacing the special-chars with something

-- **OTHER** than a space, because plain LTRIM/RTRIM will have already removed those.

-- =============================================

ALTER FUNCTION dbo.[fn_CleanTrim] (

    @Str NVARCHAR(MAX)

    , @ReplaceTabWith NVARCHAR(5) = ' '

    , @ReplaceNewlineWith NVARCHAR(5) = ' '

    , @PurgeReplaceCharsAtEnds BIT = 1

)

RETURNS NVARCHAR(MAX) AS

BEGIN

    DECLARE @Result NVARCHAR(MAX)

    --The main work (trim & initial replacements)

    SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

        LTRIM(RTRIM(@Str))  --Basic trim

        , NCHAR(9), @ReplaceTabWith), NCHAR(11), @ReplaceTabWith)   --Replace tab & vertical-tab

        , (NCHAR(13) + NCHAR(10)), @ReplaceNewlineWith) --Replace "Windows" linebreak (CR+LF)

        , NCHAR(10), @ReplaceNewlineWith), NCHAR(12), @ReplaceNewlineWith), NCHAR(13), @ReplaceNewlineWith)))   --Replace other newlines

    --If asked to trim replacement-char's from the ends & they're not both whitespaces

    IF (@PurgeReplaceCharsAtEnds = 1 AND NOT (@ReplaceTabWith = N' ' AND @ReplaceNewlineWith = N' '))

    BEGIN

        --Purge from head of string (beginning)

        WHILE (LEFT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)

            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceTabWith)/2 + 1, DATALENGTH(@Result)/2)

        WHILE (LEFT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)

            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceNewlineWith)/2 + 1, DATALENGTH(@Result)/2)

        --Purge from tail of string (end)

        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)

            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceTabWith)/2)

        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)

            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceNewlineWith)/2)

    END

    RETURN @Result

END

GO

Replace a New Line In T-SQL

/*************************************************

REPLACE A NEW LINE IN T-SQL

***************************************************/

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

      

Schema's On a Database

/*************************************************

SCHEMA ON DB

***************************************************/

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'

AS SchemaTable

FROM sys.tables

select  c.name as colomn_name , t.name as table_name , s.name as schema_name

 from sys.columns c

   inner join  sys.tables t     on c.object_id=t.object_id

   INNER JOIN sys.schemas AS s  ON t.[schema_id] = s.[schema_id]

   SELECT schema_name, schema_owner

FROM information_schema.schemata

Set Identity and Reseed

/*************************************************

SET IDENTITY + RESEED

***************************************************/

DBCC CHECKIDENT ('[DB].[SCHEMA].[TABLE]', RESEED, 0);

/******  

Although most answers are suggesting RESEED to 0, But many a times we need to just reseed to next Id available

******/

declare @max int

select @max=max([Id])from [TestTable]

if @max IS NUll   //check when max is returned as null

  SET @max = 0

DBCC CHECKIDENT ('[TestTable]', RESEED,@max)

SP_WHO2 With a Order by Clause & Monitor Server

/*************************************************

SP_WHO 2 ORDER BY

***************************************************/

CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),

      Login  VARCHAR(255),HostName  VARCHAR(255),

      BlkBy  VARCHAR(255),DBName  VARCHAR(255),

      Command VARCHAR(255),CPUTime INT,

      DiskIO INT,LastBatch VARCHAR(255),

      ProgramName VARCHAR(255),SPID2 INT,

      REQUESTID INT)

INSERT INTO #sp_who2 EXEC sp_who2

  SELECT *

    FROM #sp_who2

   WHERE DBName <> 'master' -- Add any filtering of the results here

ORDER BY status ASC -- Add any sorting of the results here

DROP TABLE #sp_who2

/*************************************************

Running Queries

***************************************************/

SELECT p.spid

     , p.status

     , p.hostname

     , p.loginame

     , p.cpu

     , r.start_time

     , r.command

     , p.program_name

     , text

  

       FROM sys.dm_exec_requests AS r,

            master.dbo.sysprocesses AS p

CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)

      WHERE p.status NOT IN ('sleeping', 'background')

        AND r.session_id = p.spid

/*************************************************

All Queries

***************************************************/

     SELECT sqltext.TEXT

          , req.session_id

          , req.status

          , req.command

          , req.cpu_time

          , req.total_elapsed_time

       FROM sys.dm_exec_requests req

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext