Ron Michael Zettlemoyer ronmichael
ronmichael
/ big-tables.sql
Created
January 21, 2013 00:01
Analyze all your tables and identify the big ones with this MSSQL script. Originally created Bill Graziano ([email protected]) of SQLTeam.com.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| declare @id int | |
| declare @type character(2) | |
| declare @pages int | |
| declare @dbname sysname | |
| declare @dbsize dec(15,0) | |
| declare @bytesperpage dec(15,0) | |
| declare @pagesperMB dec(15,0) | |
| create table #spt_space |
ronmichael
/ concat-with-xml.sql
Last active
December 11, 2015 09:49
Use XML to concatenate multiple records into a single field in MSSQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select | |
| s.name, | |
| stuff ( | |
| ( SELECT ',' + c.name AS [text()] FROM cities c where c.stateid=s.id | |
| FOR XML PATH('') ), | |
| 1, 1, '') cities | |
| from states s; | |
| select | |
| s.name, |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- query all groups | |
| SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''Group'' ') | |
| -- query all users | |
| SELECT * FROM OPENQUERY( ADSI, 'SELECT objectGuid, distinguishedName, samAccountName, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''User'' ') where samAccountName not like '%$' | |
| -- to query ALL group memberships, you'll have to cursor through | |
| -- all groups and get their members with a statement like this: | |
| select * from OPENQUERY( ADSI, 'SELECT objectGuid, name FROM ''LDAP://dc=mydomain,dc=local'' where objectClass = ''user'' and memberof=''CN=[group name],OU=[org unit],DC=mydomain,DC=local'' ') | |
NewerOlder