Useful_TSql_Tips.sql

------------------------------------------------------------------------
-- Create a Comma Delimited List Using SELECT Clause From Table Column
-- from: http://tinyurl.com/7ac6yaj (blog.sqlauthority.com)
------------------------------------------------------------------------
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM dbo.Product
SELECT @listStr
GO

-------------------------------------------
-- All relevant db objects and permissions
-------------------------------------------
;With CTE As
(
select o.name, o.type, o.type_desc, p.permission_name, p.state_desc, p.state, u.name as 'users'
from sys.objects o
left outer join sys.database_permissions p On o.object_id = p.major_id
left outer JOIN sys.sysusers u ON p.grantee_principal_id = u.uid  
where o.type Not In ('D', 'F', 'IT', 'PK', 'S', 'SQ', 'UQ')
)
select distinct name, type, type_desc, permission_name, state_desc, state,
	stuff((
        SELECT ', ' + users
        FROM CTE
	    WHERE name = c.Name 
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(2000)'),1,1,'') AS 'users'
From CTE c
order by type, name, state
return

----------------------------
-- STUFF() and FOR XML PATH
----------------------------
-- Get Tab Permissions (Form permissions excluded)
;With CTE AS 
(
Select distinct usr_FormSeq, usr_FormTabSeq, usr_AccessRightID, usr_TabName, usr_FormName
From SRSecurityDev.dbo.UserSessionRights  u
Where usr_SessionID = @SessionID
And usr_AccessRightValue = 1
And usr_FormSeq = @UIID
And usr_FormTabSeq Is Not Null
),
Summarized AS
(
Select Distinct usr_FormSeq, usr_FormTabSeq,
	stuff((
        SELECT distinct ', ' + usr_AccessRightID
        FROM CTE
	    WHERE usr_FormSeq = c.usr_FormSeq And usr_FormTabSeq = c.usr_FormTabSeq
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(2000)'),1,1,'') AS 'TabPermissions',
	usr_TabName, usr_FormName --, CanRead, CanUpdate
From CTE c
)
Select *, iif(TabPermissions Like '%A_READ%', 'Y', '') as 'CanRead',
iif(TabPermissions Like '%A_UPDATE%', 'Y', '') as 'CanUpdate'
From Summarized
Order By usr_TabName
-------------------

Declare @UIID int = 1003  -- UserInterfaceID 
Declare @SessionID varchar(40) = '9EE9353D-366A-4AA1-A014-9EC8C0D4E5EF'
;With CTE as (
Select usr_FormSeq, usr_FormTabSeq, usr_AccessRightID, usr_TabName, usr_FormName
From SRSecurityDev.dbo.UserSessionRights  u
Where usr_SessionID = @SessionID
And usr_AccessRightValue = 1
And usr_FormSeq = @UIID
)
Select Distinct usr_FormSeq, usr_FormTabSeq,
	stuff((
        SELECT distinct ', ' + usr_AccessRightID
        FROM CTE
	    WHERE usr_FormSeq = c.usr_FormSeq And usr_FormTabSeq = c.usr_FormTabSeq
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(2000)'),1,1,'') AS 'Permissions',
	usr_TabName, usr_FormName
From CTE c
Order By usr_TabName

--Why the STUFF function is used - it strips the leading comma and space
select ', ' + name -- Note the lack of column name
from sys.databases
where database_id > 4
order by name
for xml path('')

select
  stuff(
	(select ', ' + name  -- do not do an 'as columname' here or error will result
	 --add a comma and space before each string
     from sys.databases
     where database_id > 4
     order by name
     for xml path('')
	 ), 1, 2, '') as namelist;
	    --this removes the first two characters from the result

select top 10 * from master.sys.databases order by database_id
return

--good article: http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

-----------------------------------
-- Generate a comma-separated list
-----------------------------------
DECLARE @Heroes TABLE (
    [HeroName]      VARCHAR(20)
)

INSERT INTO @Heroes ( [HeroName] )
VALUES ( 'Superman' ), ( 'Batman' ), ('Ironman'), ('Wolverine')

SELECT STUFF((SELECT ',' + [HeroName]
			  FROM @Heroes
			  ORDER BY [HeroName]
			  FOR XML PATH('')), 1, 1, '') AS [Output]
 
--Output
--------------
--Batman,Ironman,Superman,Wolverine
------------------------------------

--------------
-- FileTables
--------------
select GETPATHLOCATOR('\\srwc-sql\MSSQLSERVER\ArchivesDev\iImagesFT\schema 3.0.png')

If @Action = 'GetFQImageFilePath' And @iE_ID Is Not Null 
Begin 
	Select 1 as 'RC', file_stream.GetFileNamespacePath(1,1) AS 'FQImageFilePath'
	FROM [dbo].[iElements]
	LEFT OUTER JOIN [dbo].[iImagesFT] ON iE_ImagePath = path_locator
	Where iE_ID = @iE_ID
End  

--SELECT * FROM [dbo].[iImagesFT] WHERE path_locator = 0xFCE99251B6413B4FF550522875AB94F89289AB72A0

--------
-- Misc
--------
SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' order by modify_date desc

alter login archiveUser WITH CHECK_EXPIRATION = OFF;

-------------------------------------------------
-- RESEED IDENTITY VALUE TO A NEW STARTING VALUE
-------------------------------------------------
DBCC CHECKIDENT ('TestTable', RESEED, 1)
GO

--see: http://blog.sqlauthority.com/2012/08/23/sql-server-reseting-identity-values-for-all-tables/
USE DATABASE;
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'
GO

----------------------------------------------
-- EXECUTE Permission ON Table Type User Defined Type: Event though we have Execute permission 
-- on the Stored Procedure, we still need to give Execute permission on the Table Type User Defined Type. 
-- Below is the syntax for granting execute permission on the Table Type User Defined Type
----------------------------------------------

GRANT EXECUTE ON TYPE::dbo.ttDFUpdate TO archiveUser

--Grant execute on user defined function
GRANT EXECUTE ON [dbo].[ufn_Base36Identifier] TO [archiveUserRole]

-----------------------------------
-- Change the datatype of a column
-----------------------------------

Alter table iActivity Alter Column iA_BeforeValue nvarchar(256) NULL

--------------------------------
-- Add a new column to a table
--------------------------------
Alter Table [dbo].[mScreens] Add [mS_isLinksOnly] [char](1) NOT NULL Default 'N'


------------------------------------------------------------------ 
-- Restore to Online a database that is in Recovery Pending status
------------------------------------------------------------------ 

Use Master
go
ALTER DATABASE ArchivesDev SET ONLINE

--------------------------------------------------------
-- Delete a default constraint for a column in a table
--------------------------------------------------------

Use [ArchivesFS]
go

ALTER TABLE mMetadata DROP CONSTRAINT [DF_mMetadata_mM_Qualifier]

--ALTER TABLE [dbo].[mMetadata] ADD  CONSTRAINT [DF_mMetadata_mM_Qualifier]  DEFAULT ('None') FOR [mM_Qualifier]

-------------------------------------------------------------------------------------
-- Update a table in one database from a corresponding table in a different database
-------------------------------------------------------------------------------------

update T
Set T.[iVV_SearchDisplayValue] = D.[iVV_SearchDisplayValue]
From ArchivesTest.dbo.iVocabularyValues T, ArchivesDev.dbo.iVocabularyValues D
where T.iVV_ID = D.iVV_ID

---------------------------------------------------------------
-- Copy sysdiagram from a more recent version of the database)
---------------------------------------------------------------

use [ArchivesN]
go

update sysdiagrams set definition = (select definition 
from [ArchivesFS].[dbo].[sysdiagrams] 
where principal_id =1 And diagram_id=1 and version=1)

-----------------------------------------------------
-- Get list of stored procedures matching a pattern:
-----------------------------------------------------

select name from [ArchivesDev].sys.procedures p 
WHERE p.name LIKE '%activ%'

-------------------------
-- Rename a table column
-------------------------

sp_rename 'dbo.mScreen-Metadata.mSM_MetatadataID', 'mSM_MetadataID', 'Column'
go

---------------------------------------------------------------------------------------------
-- Get a full code listing of all Stored Procedures that match a pattern (e.g., usp_Action%)
-- Run this Select statement in a query window, then copy the output into a text file
---------------------------------------------------------------------------------------------

SELECT definition + char(13) + 'GO' FROM [ArchivesN-56].sys.sql_modules s 
INNER JOIN [ArchivesN-56].sys.procedures p ON [s].[object_id] = [p].[object_id] 
WHERE p.name LIKE 'usp_Action%'

----------------------------------------------------------------------
-- List all stored procedures in one database that are not in another
----------------------------------------------------------------------

Select *
from [ArchivesN].sys.procedures P
where P.name not in  (select name from [Archives].sys.procedures P2)

-------------------------------------------------------------
-- Recreate a user if the restore does not include that user
-------------------------------------------------------------

USE [DCMI]
GO

CREATE USER [DELL1579\Jerry] FOR LOGIN [DELL1579\Jerry] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [DELL1579\Jerry]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [DELL1579\Jerry]
GO

----------------------------------------
-- List stored procedures in a database
----------------------------------------

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
and SPECIFIC_NAME like 'usp%'

--select * from sys.procedures

------------------
-- List of tables 
------------------

select TABLE_NAME from INFORMATION_SCHEMA.TABLES

--If you want the other columns:
select * from INFORMATION_SCHEMA.TABLES

------------------------------------------------------------------------
-- Find out where the MDF fils is located for an open instance in SSMS: 
-- select physical_name from sys.database_files where name = ‘master’.
------------------------------------------------------------------------

--How to find where on disk a database is located:
USE SSMANorthwind
GO
SELECT type_desc, name, physical_name from sys.database_files

-- #tsql #sqlserver