Find records where your column has numbers or not SQL Server

Well, after looking for a while online I was kind of surprised nobody have had blogged about this. Maybe I was looking for the wrong keywords I don’t know

I was able to come up with this simple way.

Find records where your column has numbers :

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) > 1

 

Find only records with number on the column
WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 0

Find records where your column does not have numbers

WHERE PATINDEX ( '%[^0-9]%' , [Field Name]) = 1

 

I hope you enjoy this.

Mass add link to Navigation Pane SharePoint 2010 Power shell

--add a link to navigation pane mass sites

$sites = Get-SPWeb "Site Here"
foreach ($site in $sites.webs)
{
$web = Get-SPWeb $Site.URL
$qlNav = $web.Navigation.QuickLaunch
$qlHeading = $qlNav | where { $_.Title -eq "Libraries" }
$linkNode = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Name", "URL", $true)
$qlHeading.Children.AddAsLast($linkNode)
$qlHeading.Update
$web.Update
$web.Dispose
}

enjoy

Hierarchy Query

This I Took from another Blog but is so cool I have to re post it,

This is to make a level hierarchy query

Only SQL server 2008 and up

with MyCTE AS
(
SELECT      
TYPEID,
ParentTypeId,
TYPE,
1 as level,
CAST(CAST(TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM          
TASKTYPE
WHERE      
(ParentTypeId  IS NULL)
UNION ALL

SELECT      
TY.TYPEID,
TY.ParentTypeId ,
TY.TYPE,
EL.level + 1,
CAST(el.SortPath + CAST(TY.TYPEID AS BINARY(4)) AS VARBINARY(8000)) AS SortPath
FROM            TASKTYPE as TY
INNER JOIN MyCTE AS el on TY.ParentTypeId  = el.TypeID
WHERE        (TY.ParentTypeId  IS NOT NULL)

)
Select Typeid, parenttypeid, type = SPACE((level-1)*4)+type , level
from MyCTE
ORDER BY SortPath

Enjoy

Edit library in SharePoint site list with PowerShell

I like simple scripts I don't really like functions and all that when I need is to run a quick update.
Here a really nice one I've modified from some smart guys
I needed to remove the property "Force Check Out" from all libraries on my site collection.
 

$sites = Get-SPWeb {Site URL}
foreach ($site in $sites.webs)
{
$web = Get-SPWeb $site.URL
$library = $web.Lists[{Library Name}]
$library.ForceCheckOut = $false
$library.Update()
$web.Update()
$web.Dispose()
}
 
Additional options here

$library.EnableVersioning = $true
$library.EnableMinorVersions = $true
$library.MajorVersionLimit = $majorVersionLimit
$library.MajorWithMinorVersionsLimit = $minorVersionLimit
$library.ForceCheckOut = $requireCheckOut

I hope it helps

Add library to SharePoint sites using power shell

Will work on your sites three, run from your top site.

$sites = Get-SPWeb <site url>
foreach ($site in $sites.webs)
{
 $listTemplate = $Site.ListTemplates["Document Library"];
 $Site.Lists.Add(<Library name>,<Library Name>,$listTemplate);
 $list = $Site.Lists[<Library Name>];
 $list.Title = <Library Title>;
 $List.DefaultItemOpen = "PreferClient";
 $list.EnableVersioning = $true;               
 $list.EnableMinorVersions = $false;
 $list.MajorVersionLimit = 0;        
 $list.ForceCheckout = $true;
 $List.OnQuickLaunch = $true;
 $List.ContentTypesEnabled = $true
 $list.Update()
 $ctToRemove = $list.ContentTypes[<Main Content Type>]
 $list.ContentTypes.Delete($ctToRemove.Id)
 $list.Update()
 $SPWeb1 = Get-SPWeb -Identity <Main Site where content type is>
 $ctToAdd = $SPWeb1.ContentTypes[<Content Type Name>];
 $ct = $List.ContentTypes.Add($ctToAdd);
 $list.Update()
 $Site.Dispose() 
 $SPWeb1.Dispose()
}

Get table size T SQL

Simple and effective your admin will love this


SELECT   DB_NAME() AS DatabaseName
   , object_name(i.object_id) AS TableName
   , ISNULL(i.name, 'HEAP') AS IndexName
   , i.index_id AS IndexID
   , i.type_desc AS IndexType
   , p.partition_number AS PartitionNo
   , p.[rows] AS NumRows
   , au.type_desc AS InType
   , au.total_pages AS NumPages
   , au.total_pages * 8 AS TotKBs
   , au.used_pages * 8 AS UsedKBs
   , au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.[type] in (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
end = au.container_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.is_ms_shipped <> 1
ORDER BY TableName, i.index_id

Database in lock state?

With this script you will get your panic attack under control

Use <Database>

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE

Easy way to Shrink databases Log

Easy to set up as an scheduled task

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Same apply to the database file

USE [RC_BI_DW]  DBCC SHRINKFILE (N<Database file name> , 0, TRUNCATEONLY)

SharePoint Add Gropup to Site using Power Shell

Simple Script to add an existing group to a site or sites.

$sites = Get-SPWeb "Site url"
foreach ($site in $sites.webs)
{
$site.AssociatedGroups.Add($site.SiteGroups["Group Name"]);
$GroupAccount = $site.SiteGroups["Group Name"]
$timeoffassignment = New-Object Microsoft.SharePoint.SPRoleAssignment($GroupAccount)
$timeoffrole = $site.RoleDefinitions["Read"]
$timeoffassignment.RoleDefinitionBindings.Add($timeoffrole)
$site.RoleAssignments.Add($timeoffassignment)
$site.Update()
$site.Dispose()
}

So you wear multiple hats?

If you are one of those multi hat kind of person you might want to take a look here.

I hope I can help with some tricks or tips that will make your life a little easier.

Some of the sections here will cover:

·         SharePoint
·         SQL Server
·         VB Script
·         Batch files
·         PCI Compliance