PowerShell: Update target Audience of a SharePoint 2010 Web Part

 # Get reference to the SPSite object  
 $SPWeb = Get-SPWeb "http://sharepoint"  
 # Get reference to the landing page. Since it is a publishing site so default.aspx is the landing page   
 $page = $SPWeb.GetFile("Pages/index.aspx")   
 ### Uncomment the lines below to perform the update  
 #$page.CheckOut()   
 # //Get reference to the webpartmanager class   
 $webpartmanager = $SPWeb.GetLimitedWebPartManager("Pages/index.aspx", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)    
 # Double check the web part that you wish to update. Iterate through webparts in webpartmanager class   
 for ($i=0;$i -lt $webpartmanager.WebParts.Count;$i++)   
 {    
      #write-host $webpartmanager.WebParts[$i].title  
      # Check for the name of required web part    
      if($webpartmanager.WebParts[$i].title -eq "Content Query Web Part")    
      {    
           #Get reference to the web part   
           $wp = $webpartmanager.WebParts[$i];   
           write-host $wp.title " " $i  
           # If there is any existing filter, copy it  
           write-host $wp.AuthorizationFilter  
           # In the filter (empty: ;;;;;;), first two semicolons: individual users as target audience; second two semicolons: distribution list; last two semicolons: can be replaced to include sharepoint groups    
           # e.g. "174e7e9d-ff39-4856-85a6-9247b02e59fb,124f1234-3998-4b54-83d1-abc4048fb9b4;;;;"  
           ### Optionally perform direct update while in for loop  
           # Other properties e.g. chrome property   
           #$wp.ChromeType="TitleAndBorder";   
           # Save changes to webpartmanager. This step is necessary. Otherwise changes wont be reflected   
           #$webpartmanager.SaveChanges($wp);   
  }    
  }    
 ### Uncomment the lines below to perform the update  
 #$wp = $webpartmanager.WebParts[26];  
 #write-host "begin update: " $wp.Title  
 #$wp.AuthorizationFilter = "174e7e9d-ff39-4856-85a6-9247b02e59fb,124f1234-3998-4b54-83d1-abc4048fb9b4,3a87dc31-9067-4996-b4c2-5efc6ff3eab7,68d2a840-bdbb-44ee-aacd-395b5551c473,7add71c2-dbea-4218-b8ff-f6b112f225e4,577550be-616e-41cd-90a4-493c030ddd72,9f113155-7c70-4d18-9417-851d82ba8b6a,c664006b-ec7f-4798-9acc-c48766be68a8,e26f7ece-615c-474d-9db9-0f60953e113a,ee912564-4f77-43c4-aced-55d387bc4e8e,51d2b9df-7ca3-42ad-a307-f9fdf28fe417,59500291-5912-47c0-b6a2-9c9adf24f2da;;;;";  
 #$webpartmanager.SaveChanges($wp);  
 #write-host "saving webpart target audience done...";  
 ### Uncomment the lines below to check in and Publish the page   
 #$page.CheckIn("Update Audience(batch powershell)")   
 #$page.Publish("Update Audience batch powershell)")   
 #$SPWeb.Update();    
 # Dispose SPWeb object   
 $SPWeb.Dispose();   

PowerShell list SharePoint Global Navigation headers and subheader links

This script will traverse the top navigation bar links in a SharePoint site and retrieve all the main headers plus dropdown links, and optionally list the link URLs.

Tried on SharePoint publishing sites.
How do you know if publishing is turned on?
If you see the Navigation link instead of Top Link Bar / Quick Launch, the feature is turned on.

SharePoint PowerShell: Adding complex rules to existing audience

Here is a PowerShell script to modify an existing Audience.

As you know, in SP2010, audience rules configured from the Central Administration can only have 2 choices:
1. Meet any condition listed
2. Meet ALL conditions listed

So if I want to have more complex conditions like nested conditions, I will either need to write a program (C#) to do it, or run some powershell commands in the web front end.

DO take note that once an audience is programmatically modified, you WILL NOT BE ABLE TO MAKE CHANGES from the Central Administation console anymore.

Sample script below:

SQL Find stored procedure containing text

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

Copied from
http://stackoverflow.com/questions/5079457/how-do-i-find-a-stored-procedure-containing-text

Super AWESOME! SQL find value from ALL tables

This great script was originally posted by fullparam. Read the original post here

I did some modifications because I had to run it on SQL Server 2000 - It's obsolete I know!


DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%1k9ovp.q%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(2000),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256),@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
SET @TableName = ''
--DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
IF OBJECT_ID('tempdb..#ColumnNameTable') IS NOT NULL DROP TABLE #ColumnNameTable
CREATE TABLE #ColumnNameTable (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))
WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
)
IF @TableName IS NOT NULL
BEGIN
DECLARE @sql VARCHAR(2000)
SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)
AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)
AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
INSERT INTO #ColumnNameTable --@ColumnNameTable
EXEC (@sql)
WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM #ColumnNameTable)--@ColumnNameTable)
BEGIN
PRINT @ColumnName
SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM #ColumnNameTable--@ColumnNameTable
SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),'''
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
INSERT INTO #Results
EXEC(@sql)
IF @@ROWCOUNT > 0 IF @FullRowResult = 1
BEGIN
SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
' FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))'
WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
EXEC(@sql)
END
DELETE FROM #ColumnNameTable /*@ColumnNameTable*/ WHERE COLUMN_NAME = @ColumnName
END
END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType