Skip to content

Instantly share code, notes, and snippets.

@jrdmb

jrdmb/Useful_TSql_Tips.sql

Last active August 29, 2015 14:19
Show Gist options
  • Save jrdmb/a078e2a87e1ee704179e to your computer and use it in GitHub Desktop.
Save jrdmb/a078e2a87e1ee704179e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment