working on it ...

Filters

Explore Public Snippets

Sort by

Found 45k snippets matching: data

    public by fabio.nosenzo @ Amazon_AWS_Python_API  588172  5  8  -1

    Amazon S3 Aws - S3Connection object from python boto API

    This snippet is about the S3Connection object provided by python boto package for Amazon AWS API. Here the __init__ method is listed with all its variables. The AWSAuthConnection object is initialized and it handles all the data for managing requests and response from Amazon AWS service. Path: [boto/s3/connection.py] Learn more about S3Con
    import boto.utils
    from boto.connection import AWSAuthConnection
    
    class S3Connection(AWSAuthConnection):
        DefaultHost = boto.config.get('s3', 'host', 's3.amazonaws.com')
        DefaultCallingFormat = boto.config.get('s3', 'calling_format', 'boto.s3.connection.SubdomainCallingFormat')
        QueryString = 'Signature=%s&Expires=%d&AWSAccessKeyId=%s'
    
        def __init__(self, aws_access_key_id=None, aws_secret_access_key=None,
                     is_secure=True, port=None, proxy=None, proxy_port=None,
                     proxy_user=None, proxy_pass=None,
                     host=DefaultHost, debug=0, https_connection_factory=None,
                     calling_format=DefaultCallingFormat, path='/',
                     provider='aws', bucket_class=Bucket, security_token=None,
                     suppress_consec_slashes=True, anon=False,
                     validate_certs=None):
            if isinstance(calling_format, str):
                calling_format=boto.utils.find_class(calling_format)()
            self.calling_format = calling_format
            self.bucket_class = bucket_class
            self.anon = anon
            AWSAuthConnection.__init__(self, host,
                    aws_access_key_id, aws_secret_access_key,
                    is_secure, port, proxy, proxy_port, proxy_user, proxy_pass,
                    debug=debug, https_connection_factory=https_connection_factory,
                    path=path, provider=provider, security_token=security_token,
                    suppress_consec_slashes=suppress_consec_slashes,
                    validate_certs=validate_certs)

    public by lbottaro  463516  0  7  0

    Excel function to calculate the average of positive data in range

    This excel function calculate the average of a specific range of cells, regarding only positive values. Zero or less than zero value won't be used in average calculation. The italian version of this snippet can be used as well on OpenOffice 3.
    =SUMIF(B2:B6;">0")/MAX(COUNTIF(B2:B6;">0");1)
    
    # Italian version
    =SOMMA.SE(B2:B6;">0")/MAX(CONTA.SE(B2:B6;">0");1)

    public by sTiLL-iLL @ SniPitz-KND  378514  6  9  29

    set.js... its my "Set", it runs this code-block

    Full on structure type implimentation of a Set
    
    // Set....   hut-1, hut-2, hike!
    
    function Set() {
       this.dStr = [];
       this.size = size;
       this.unionOf = unionOf;
       this.contains = contains;
       this.subSet = subSet;
       this.getDiff = getDiff;
       this.removeItem = removeItem;
       this.intersection = intersection;
       this.addItem = addItem;
       this.show = show;
    }
    
    function show() {
       return "[" + this.dStr + "]";
    }
    
    function size() {
       return this.dStr.length;
    }
    
    
    
    function addItem(dta) {
       if (this.dStr.indexOf(dta) < 0) {
          this.dStr.push(dta);
          return true;
       } 
       else {
          return false;
       }
    }
    
    function removeItem(dta) {
       var pstn = this.dStr.indexOf(dta);
       if (pstn > -1) {
          this.dStr.splice(pstn, 1);
          return true;
       }
       else {
          return false;
       }
    }
    
    function contains(dta) {
       if (this.dStr.indexOf(dta) > -1) {
          return true;
       }
       else {
          return false;
       }
    }
    
    function unionOf(set) {
       var tmp = new Set(),
           i = 0;
       for (;i < this.dStr.length; ++i) {
          tmp.add(this.dStr[i]);
       }
       i = 0;
       for (;i < set.dStr.length; ++i) {
          if (!tmp.contains(set.dStr[i])) {
             tmp.dStr.push(set.dStr[i]);
          }
       }
       return tmp;
    }
    
    function intersection(set) {
       var tSet = new Set();
       for (var i = 0; i < this.dStr.length; ++i) {
          if (set.contains(this.dStr[i])) {
             tSet.add(this.dStr[i]);
          }
       }
       return tmp;
    }
    
    function subSet(set) {
       if (this.size() > set.size()) {
          return false;
       }
       else {
          for each (var m in this.dStr) {
             if (!set.contains(m)) {
                return false;
             }
          }
       }
       return true;
    }
    i = 0;
    function difference(set) {
       var tmp = new Set();
       for (;i < this.dStor.length; ++i) {
          if (!set.contains(this.dStor[i])) {
             tmp.add(this.dStor[i]);
          }
       }
       return tmp;
    }  
    
    
    

    public by przodownikR1  394858  1  3  0

    example create datasource in javaConfig with full desc and params

    example create datasource in javaConfig with full desc and params: db_config_full.java
    @Bean
      public DataSource dataSource() {
        final DataSource dataSource = new DataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.username"));
        dataSource.setPassword(env.getProperty("jdbc.password"));
        dataSource.setTestOnBorrow(true);
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnReturn(false);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setValidationInterval(30000);
        dataSource.setTimeBetweenEvictionRunsMillis(5000);
        dataSource.setMaxActive(100);
        dataSource.setMinIdle(10);
        dataSource.setMaxWait(10000);
        dataSource.setInitialSize(10);
        dataSource.setRemoveAbandonedTimeout(3600);
        dataSource.setRemoveAbandoned(true);
        dataSource.setLogAbandoned(true);
        dataSource.setMinEvictableIdleTimeMillis(30000);
        return dataSource;
      }
    
    

    public by PLAT.ONE @ PLAT.ONE  324336  3  6  0

    How to save a custom property on PLAT.ONE, aka to enhance the data model of a device

    Custom properties are key-value pairs associated to an entity in PLAT.ONE. You can attach at runtime those new properties
    import java.util.ArrayList;
    import java.util.List;
    
    import com.abodata.plat1.nodeInfo.WSNNodeType;
    import com.abodata.plat1.system.ICredentialUtils;
    import com.abodata.plat1.utils.APIFilter;
    import com.abodata.plat1.velocity.client.IPlatoneAPI;
    import com.abodata.plat1.ws.velocity.IProxyCustomProperty;
    import com.abodata.plat1.ws.velocity.IProxyPhysicalNode;
    import com.abodata.plat1.ws.velocity.IProxyPlatUser;
    import com.abodata.plat1.ws.velocityInfo.ProxyObjType;
    import com.abodata.plat1.properties.CPAllowable;
    
    public class AddCustomPropertyExample {
    
    	public void addProperty() throws Exception {
    		
    		IPlatoneAPI apiMan = YourServiceActivator.getApiMan();
    		ICredentialUtils creds = YourServiceActivator.getCredentialUtils();
    		
    		//let's retrieve the first available camera:
    		List<APIFilter> filters = new ArrayList<APIFilter>();
    		filters.add(new APIFilter("type", new String[] { WSNNodeType.ENDPOINT.getType() + "", WSNNodeType.ROUTER.getType() + "" }));
    		List<IProxyPhysicalNode> cameras = apiMan.DB_GetVisiblePhysicalNode(null, filters, true, 0, 1);
    		IProxyPhysicalNode camera =	cameras.get(0);
    		
    		//get the current user:
    		IProxyPlatUser curUser = apiMan.DB_GetPlatUser(creds.getUserName(), creds.getPsw());
    		
    		//add a property to the camera:
    		IProxyCustomProperty p = apiMan.DB_AddCustomProperty(creds.getAppGardenId(), "resolution", false, "1280x768", 
    				ProxyObjType.customProperty, curUser.getId(), camera.getId(), ProxyObjType.physNode, CPAllowable.all, 
    				CPAllowable.all);
    		if (p != null)
    			System.out.println("Property added!");
    		else
    			System.out.println("Something wrong...");
    	}
    }
    

    public by mprisznyak  285648  0  7  8

    Use named tuples to pass data around

    The Bad, Ugly Villain and the Good, Handsome Hero
    """
        You need to return a bunch of data in tuples from a function
        For example, the data could be a lot of, possibly unrelated, parameters of some software system
    
        Consider passing full-blown objects around instead of tuples,  of course.
        API or memory constraints sometimes don't make that possible.
    """
    
    
    def return_info_in_the_wrong_way(number_of_persons):
    
        persons = []
        for i in xrange(number_of_persons):
            name = "Person {0}".format(i)
            age  = 20 + i*2
            sex  = i % 2
            residence = "Never Ville {0}".format(i)
            salary = 50000+ (i-8)*200
            if i % 2 == 1:
                pet_name = "Cat {0}".format(i)
            else:
                pet_name = "Dog {0}".format(i)
            car_make = "Car {0}".format(i % 3)
            owned_computers = int(sex*4 / 3) + int(salary-48000)/110
            persons.append((name, age, sex, residence, salary, pet_name, car_make, owned_computers))
    
        return tuple(persons)
    
    
    from collections import namedtuple
    Person = namedtuple('Person', 'name age sex residence salary pet_name car_make owned_computers')
    
    def return_info_in_a_better_way(number_of_persons):
    
        persons = []
        for i in xrange(number_of_persons):
            name = "Person {0}".format(i)
            age  = 20 + i*2
            sex  = i % 2
            residence = "Never Ville {0}".format(i)
            salary = 50000+ (i-8)*200
            if i % 2 == 1:
                pet_name = "Cat {0}".format(i)
            else:
                pet_name = "Dog {0}".format(i)
            car_make = "Car {0}".format(i % 3)
            owned_computers = int(sex*4 / 3) + int(salary-48000)/110
            person = Person(name, age, sex, residence, salary, pet_name, car_make, owned_computers )
            persons.append(person)
    
        return tuple(persons)
    
    
    
    
    if __name__ == "__main__":
    
        print "********** in the wrong way ****************"
        person = return_info_in_the_wrong_way(11)[7]
        print "the 8th person's pet called... ",  person[5]
        print "    is lost in ", person[3]
        print "********** in a better way *****************"
        person = return_info_in_a_better_way(11)[7]
        # readibility matters!!
        print "the 8th person's pet called... ", person.pet_name
        print "    is lost in ", person.residence
        # and you could still use numeric indexes if you do need to:
        print "the 8th person's pet called... ", person[5]
        print "    is lost in ", person[3]

    public by cghersi  338829  1  6  0

    ASP.NET Performance monitors

    The following XML file can be used to create a new user defined Data Collector Set in the Performance Monitor Tool for Windows servers. It checks the most important performance counters for the right execution of ASP.NET Web applications. The performance counters taken into account are those reported in this article: http://mvolo.com/fix-the-3-hi
    <?xml version="1.0" encoding="UTF-16"?>
    <DataCollectorSet>
    	<Status>0</Status>
    	<Duration>600</Duration>
    	<Description>Genera un rapporto con informazioni dettagliate sullo stato delle risorse hardware locali, sui tempi di risposta del sistema e sui processi eseguiti nel computer locale. Tali informazioni consentono di identificare le possibili cause dei problemi di prestazioni. Per eseguire questo Insieme agenti di raccolta dati, è necessario essere almeno membri del gruppo Administrators locale o di un gruppo equivalente.</Description>
    	<DescriptionUnresolved>Genera un rapporto con informazioni dettagliate sullo stato delle risorse hardware locali, sui tempi di risposta del sistema e sui processi eseguiti nel computer locale. Tali informazioni consentono di identificare le possibili cause dei problemi di prestazioni. Per eseguire questo Insieme agenti di raccolta dati, è necessario essere almeno membri del gruppo Administrators locale o di un gruppo equivalente.</DescriptionUnresolved>
    	<DisplayName>
    	</DisplayName>
    	<DisplayNameUnresolved>
    	</DisplayNameUnresolved>
    	<SchedulesEnabled>-1</SchedulesEnabled>
    	<Keyword>CPU</Keyword>
    	<Keyword>Memory</Keyword>
    	<Keyword>Disk</Keyword>
    	<Keyword>Network</Keyword>
    	<Keyword>Performance</Keyword>
    	<LatestOutputLocation>C:\PerfLogs\Admin\ASP.NET\ZIOJ-PC_20130727-000002</LatestOutputLocation>
    	<Name>ASP.NET</Name>
    	<OutputLocation>C:\PerfLogs\Admin\ASP.NET\ZIOJ-PC_20130727-000003</OutputLocation>
    	<RootPath>%systemdrive%\PerfLogs\Admin\ASP.NET</RootPath>
    	<Segment>0</Segment>
    	<SegmentMaxDuration>0</SegmentMaxDuration>
    	<SegmentMaxSize>0</SegmentMaxSize>
    	<SerialNumber>3</SerialNumber>
    	<Server>
    	</Server>
    	<Subdirectory>
    	</Subdirectory>
    	<SubdirectoryFormat>3</SubdirectoryFormat>
    	<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
    	<Task>
    	</Task>
    	<TaskRunAsSelf>0</TaskRunAsSelf>
    	<TaskArguments>
    	</TaskArguments>
    	<TaskUserTextArguments>
    	</TaskUserTextArguments>
    	<UserAccount>SYSTEM</UserAccount>
    	<Security>O:BAG:S-1-5-21-2577573897-3235701343-559641010-513D:AI(A;;FA;;;SY)(A;;FA;;;BA)(A;;FR;;;LU)(A;;0x1301ff;;;S-1-5-80-2661322625-712705077-2999183737-3043590567-590698655)(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200ab;;;LU)(A;ID;FR;;;AU)(A;ID;FR;;;LS)(A;ID;FR;;;NS)</Security>
    	<StopOnCompletion>0</StopOnCompletion>
    	<TraceDataCollector>
    		<DataCollectorType>1</DataCollectorType>
    		<Name>NT Kernel</Name>
    		<FileName>NtKernel</FileName>
    		<FileNameFormat>0</FileNameFormat>
    		<FileNameFormatPattern>
    		</FileNameFormatPattern>
    		<LogAppend>0</LogAppend>
    		<LogCircular>0</LogCircular>
    		<LogOverwrite>0</LogOverwrite>
    		<LatestOutputLocation>C:\PerfLogs\Admin\ASP.NET\ZIOJ-PC_20130727-000002\NtKernel.etl</LatestOutputLocation>
    		<Guid>{00000000-0000-0000-0000-000000000000}</Guid>
    		<BufferSize>64</BufferSize>
    		<BuffersLost>0</BuffersLost>
    		<BuffersWritten>0</BuffersWritten>
    		<ClockType>1</ClockType>
    		<EventsLost>0</EventsLost>
    		<ExtendedModes>0</ExtendedModes>
    		<FlushTimer>0</FlushTimer>
    		<FreeBuffers>0</FreeBuffers>
    		<MaximumBuffers>200</MaximumBuffers>
    		<MinimumBuffers>0</MinimumBuffers>
    		<NumberOfBuffers>0</NumberOfBuffers>
    		<PreallocateFile>0</PreallocateFile>
    		<ProcessMode>0</ProcessMode>
    		<RealTimeBuffersLost>0</RealTimeBuffersLost>
    		<SessionName>NT Kernel Logger</SessionName>
    		<SessionThreadId>0</SessionThreadId>
    		<StreamMode>1</StreamMode>
    		<TraceDataProvider>
    			<DisplayName>{9E814AAD-3204-11D2-9A82-006008A86939}</DisplayName>
    			<FilterEnabled>0</FilterEnabled>
    			<FilterType>0</FilterType>
    			<Level>
    				<Description>Gli eventi fino a questo livello sono attivati</Description>
    				<ValueMapType>1</ValueMapType>
    				<Value>0</Value>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x0</Value>
    				</ValueMapItem>
    			</Level>
    			<KeywordsAny>
    				<Description>Gli eventi con almeno una delle parole chiave specificate sono attivati</Description>
    				<ValueMapType>2</ValueMapType>
    				<Value>0x10303</Value>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x1</Value>
    				</ValueMapItem>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x2</Value>
    				</ValueMapItem>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x100</Value>
    				</ValueMapItem>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x200</Value>
    				</ValueMapItem>
    				<ValueMapItem>
    					<Key>
    					</Key>
    					<Description>
    					</Description>
    					<Enabled>-1</Enabled>
    					<Value>0x10000</Value>
    				</ValueMapItem>
    			</KeywordsAny>
    			<KeywordsAll>
    				<Description>Gli eventi con tutte le parole chiave specificate sono attivati</Description>
    				<ValueMapType>2</ValueMapType>
    				<Value>0x0</Value>
    			</KeywordsAll>
    			<Properties>
    				<Description>I campi dati aggiuntivi specificati verranno raccolti con ogni evento</Description>
    				<ValueMapType>2</ValueMapType>
    				<Value>0</Value>
    			</Properties>
    			<Guid>{9E814AAD-3204-11D2-9A82-006008A86939}</Guid>
    		</TraceDataProvider>
    	</TraceDataCollector>
    	<PerformanceCounterDataCollector>
    		<DataCollectorType>0</DataCollectorType>
    		<Name>Performance Counter</Name>
    		<FileName>Performance Counter</FileName>
    		<FileNameFormat>1</FileNameFormat>
    		<FileNameFormatPattern>yyyyMMdd</FileNameFormatPattern>
    		<LogAppend>-1</LogAppend>
    		<LogCircular>0</LogCircular>
    		<LogOverwrite>0</LogOverwrite>
    		<LatestOutputLocation>C:\PerfLogs\Admin\ASP.NET\ZIOJ-PC_20130727-000002\Performance Counter20130727.csv</LatestOutputLocation>
    		<DataSourceName>
    		</DataSourceName>
    		<SampleInterval>1</SampleInterval>
    		<SegmentMaxRecords>0</SegmentMaxRecords>
    		<LogFileFormat>0</LogFileFormat>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\% Managed Processor Time (estimated)</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Viewstate MAC Validation Failure</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors During Compilation</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors During Execution</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors During Preprocessing</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors Unhandled During Execution</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors Unhandled During Execution/Sec</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Audit Failure Events Raised</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Error Events Raised</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Error Events Raised/Sec</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Infrastructure Error Events Raised</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Infrastructure Error Events Raised/Sec</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Request Error Events Raised</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Request Error Events Raised/Sec</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors Total</Counter>
    		<Counter>\ASP.NET Apps v4.0.30319(__Total__)\Errors Total/Sec</Counter>
    		<Counter>\ASP.NET v4.0.30319\Error Events Raised</Counter>
    		<Counter>\ASP.NET v4.0.30319\Infrastructure Error Events Raised</Counter>
    		<Counter>\ASP.NET v4.0.30319\Request Error Events Raised</Counter>
    		<Counter>\.NET CLR Exceptions\*</Counter>
    		<Counter>\.NET CLR Memory(_Global_)\% Time in GC</Counter>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\% Tempo processore gestito (stimato)</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errore di convalida MAC ViewState</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errori di compilazione</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errori di esecuzione.</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errori di pre-elaborazione</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errori non gestiti durante l'esecuzione</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Errori non gestiti durante l'esecuzione/sec</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore di controllo generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore generati/sec</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore infrastruttura generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore infrastruttura generati/sec</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore richiesta generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Eventi errore richiesta generati/sec</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Totale errori</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET Apps v4.0.30319(__Total__)\Totale errori/sec</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET v4.0.30319\Eventi errore generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET v4.0.30319\Eventi errore infrastruttura generati</CounterDisplayName>
    		<CounterDisplayName>\ASP.NET v4.0.30319\Eventi errore richiesta generati</CounterDisplayName>
    		<CounterDisplayName>\Eccezioni CLR .NET\*</CounterDisplayName>
    		<CounterDisplayName>\Memoria CLR .NET(_Global_)\Percentuale tempo in GC</CounterDisplayName>
    	</PerformanceCounterDataCollector>
    	<DataManager>
    		<Enabled>-1</Enabled>
    		<CheckBeforeRunning>-1</CheckBeforeRunning>
    		<MinFreeDisk>200</MinFreeDisk>
    		<MaxSize>1024</MaxSize>
    		<MaxFolderCount>100</MaxFolderCount>
    		<ResourcePolicy>0</ResourcePolicy>
    		<ReportFileName>report.html</ReportFileName>
    		<RuleTargetFileName>report.xml</RuleTargetFileName>
    		<EventsFileName>
    		</EventsFileName>
    		<Rules>
    			<Logging level="15" file="rules.log">
    			</Logging>
    			<Import file="%systemroot%\pla\rules\Rules.System.Common.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.Summary.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.Performance.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.CPU.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.Network.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.Disk.xml">
    			</Import>
    			<Import file="%systemroot%\pla\rules\Rules.System.Memory.xml">
    			</Import>
    		</Rules>
    		<ReportSchema>
    			<Report name="systemPerformance" version="1" threshold="100">
    				<Import file="%systemroot%\pla\reports\Report.System.Common.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.Summary.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.Performance.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.CPU.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.Network.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.Disk.xml">
    				</Import>
    				<Import file="%systemroot%\pla\reports\Report.System.Memory.xml">
    				</Import>
    			</Report>
    		</ReportSchema>
    		<FolderAction>
    			<Size>0</Size>
    			<Age>1</Age>
    			<Actions>3</Actions>
    			<SendCabTo>
    			</SendCabTo>
    		</FolderAction>
    		<FolderAction>
    			<Size>0</Size>
    			<Age>56</Age>
    			<Actions>8</Actions>
    			<SendCabTo>
    			</SendCabTo>
    		</FolderAction>
    		<FolderAction>
    			<Size>0</Size>
    			<Age>168</Age>
    			<Actions>26</Actions>
    			<SendCabTo>
    			</SendCabTo>
    		</FolderAction>
    	</DataManager>
    	<Value name="PerformanceMonitorView" type="document">
    		<OBJECT ID="DISystemMonitor" CLASSID="CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347">
    			<PARAM NAME="CounterCount" VALUE="4">
    			</PARAM>
    			<PARAM NAME="Counter00001.Path" VALUE="\Processor(_Total)\% Processor Time">
    			</PARAM>
    			<PARAM NAME="Counter00001.Color" VALUE="255">
    			</PARAM>
    			<PARAM NAME="Counter00001.Width" VALUE="2">
    			</PARAM>
    			<PARAM NAME="Counter00001.LineStyle" VALUE="0">
    			</PARAM>
    			<PARAM NAME="Counter00001.ScaleFactor" VALUE="0">
    			</PARAM>
    			<PARAM NAME="Counter00001.Show" VALUE="1">
    			</PARAM>
    			<PARAM NAME="Counter00001.Selected" VALUE="1">
    			</PARAM>
    			<PARAM NAME="Counter00002.Path" VALUE="\Memory\Pages/sec">
    			</PARAM>
    			<PARAM NAME="Counter00002.Color" VALUE="65280">
    			</PARAM>
    			<PARAM NAME="Counter00002.Width" VALUE="1">
    			</PARAM>
    			<PARAM NAME="Counter00003.Path" VALUE="\PhysicalDisk(_Total)\Avg. Disk sec/Read">
    			</PARAM>
    			<PARAM NAME="Counter00003.Color" VALUE="16711680">
    			</PARAM>
    			<PARAM NAME="Counter00003.Width" VALUE="1">
    			</PARAM>
    			<PARAM NAME="Counter00004.Path" VALUE="\PhysicalDisk(_Total)\Avg. Disk sec/Write">
    			</PARAM>
    			<PARAM NAME="Counter00004.Color" VALUE="55295">
    			</PARAM>
    			<PARAM NAME="Counter00004.Width" VALUE="1">
    			</PARAM>
    		</OBJECT>
    	</Value>
    </DataCollectorSet>
    

    public by cghersi  328013  0  6  1

    Retrieve useful information regarding indexes in MS SQL Server

    This script helps the DBA to retrieve valuable information regarding indexes. Here the complete explanation of the output fields: object_type: type of object (will either be "U" for User-Defined Table or "V" for View) database_name: name of the database schema_name: name of the schema object_name: name of the object create_date: date the obje
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF
    SET ARITHABORT OFF
    SET ARITHIGNORE ON
    SET TEXTSIZE 2147483647
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Declarations / Sets: Declare And Set Variables
    -----------------------------------------------------------------------------------------------------------------------------
    
    DECLARE @Database_ID AS SMALLINT
    
    
    SET @Database_ID = DB_ID ()
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Error Trapping: Check If Temp Table(s) Already Exist(s) And Drop If Applicable
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Index Key, Include Key, And Filter Definition Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 sqI.[object_id]
    	,sqI.index_id
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.index_key), 1, 2, N'') AS index_key
    	,STUFF (CONVERT (NVARCHAR (MAX), sqI.include_key), 1, 2, N'') AS include_key
    	,sqI.filter_definition
    	,RANK () OVER
    				(
    					ORDER BY
    						 sqI.[object_id]
    						,CONVERT (NVARCHAR (MAX), sqI.index_key)
    						,sqI.filter_definition
    				) AS dupe_rank
    INTO
    	dbo.#temp_index_breakdown_keys_filters
    FROM
    
    	(
    		SELECT
    			 I.[object_id]
    			,I.index_id
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END)
    					+ N' '
    					+ (CASE
    							WHEN IC.is_descending_key = 0 THEN N'[A]'
    							WHEN IC.is_descending_key = 1 THEN N'[D]'
    							ELSE N'[N/A]'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 0
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS index_key
    			,(
    				SELECT
    					  N', '
    					+ C.name
    					+ N' • '
    					+ TYPE_NAME (C.user_type_id)
    					+ ISNULL ((N': [ ' + (CASE
    											WHEN C.system_type_id <> C.user_type_id THEN LOWER (TYPE_NAME (C.system_type_id))
    											END) + N' ]'), N'')
    					+ N' '
    					+ (CASE
    							WHEN TY.name NOT IN (N'bigint', N'bit', N'date', N'datetime', N'datetime2', N'datetimeoffset', N'decimal', N'float', N'int', N'money', N'numeric', N'real', N'smalldatetime', N'smallint', N'smallmoney', N'time', N'tinyint') THEN CONVERT (NVARCHAR (30), C.max_length)
    							ELSE CONVERT (NVARCHAR (30), C.max_length) + N' (' + CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Precision')) + N',' + ISNULL (CONVERT (NVARCHAR (30), COLUMNPROPERTY (C.[object_id], C.name, 'Scale')), 0) + N')'
    							END) AS [text()]
    				FROM
    					sys.index_columns IC
    					INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    						AND C.column_id = IC.column_id
    					INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
    				WHERE
    					IC.is_included_column = 1
    					AND IC.[object_id] = I.[object_id]
    					AND IC.index_id = I.index_id
    				ORDER BY
    					IC.key_ordinal
    				FOR
    					 XML PATH ('')
    					,TYPE
    			 ) AS include_key
    			,I.filter_definition
    		FROM
    			sys.indexes I
    	) sqI
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Table Insert: Insert Size Values Into Temp Table
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 DDPS.[object_id]
    	,DDPS.index_id
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.row_count
    			END) AS [rows]
    	,SUM (DDPS.reserved_page_count) AS total_pages
    	,SUM (DDPS.used_page_count) AS used_pages
    	,SUM (CASE
    			WHEN DDPS.index_id < 2 THEN DDPS.in_row_data_page_count + DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			ELSE DDPS.lob_used_page_count + DDPS.row_overflow_used_page_count
    			END) AS data_pages
    INTO
    	dbo.#temp_index_breakdown_size_info
    FROM
    	sys.dm_db_partition_stats DDPS
    GROUP BY
    	 DDPS.[object_id]
    	,DDPS.index_id
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Main Query: Final Display / Output
    -----------------------------------------------------------------------------------------------------------------------------
    
    SELECT
    	 (CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.[type]
    		ELSE ''
    		END) AS object_type
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN DB_NAME ()
    		ELSE ''
    		END) AS [database_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN SCHEMA_NAME (sqBAQ.[schema_id])
    		ELSE ''
    		END) AS [schema_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN OBJECT_NAME (sqBAQ.[object_id])
    		ELSE ''
    		END) AS [object_name]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN sqBAQ.create_date
    		ELSE ''
    		END) AS create_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN caMDKL.modify_date
    		ELSE ''
    		END) AS modify_date
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqBAQ.[rows]), 1)), 4, 22))
    		ELSE ''
    		END) AS [rows]
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.total_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS total_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.used_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS used_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.unused_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS unused_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.data_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS data_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (25), CONVERT (MONEY, (sqBAQ.index_pages * 8) / 1024.0), 1)
    		ELSE ''
    		END) AS index_mb
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.data_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_data
    	,(CASE
    		WHEN sqBAQ.row_filter = 1 THEN CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.index_pages + .0) / sqBAQ.used_pages) * 100, 0)))
    		ELSE ''
    		END) AS pct_index
    	,sqBAQ.type_desc AS index_type
    	,ISNULL (sqBAQ.index_name, '') AS index_name
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		WHEN sqBAQ.is_primary_key = 0 AND sqBAQ.is_unique = 0 THEN REPLICATE ('.', 6)
    		WHEN sqBAQ.is_system_named = 0 THEN 'No'
    		WHEN sqBAQ.is_system_named = 1 THEN 'Yes'
    		ELSE ''
    		END) AS system_named
    	,(CASE
    		WHEN sqBAQ.is_primary_key = 1 THEN 'Yes'
    		ELSE ''
    		END) AS is_pk
    	,(CASE
    		WHEN sqBAQ.is_unique_constraint = 1 THEN 'C'
    		WHEN sqBAQ.is_unique = 1 THEN 'I'
    		ELSE ''
    		END) AS [unique]
    	,(CASE
    		WHEN sqBAQ.is_disabled = 1 THEN 'Yes'
    		ELSE ''
    		END) AS [disabled]
    	,(CASE
    		WHEN sqBAQ.is_hypothetical = 1 THEN 'Yes'
    		ELSE ''
    		END) AS hypothetical
    	,REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, sqCC.total_columns), 1)), 4, 23)) AS total_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.[index_columns]), 1)), 4, 23))
    		END) AS [index_columns]
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, caMDKL.include_columns), 1)), 4, 23))
    		END) AS include_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.[index_columns] / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS index_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, (caMDKL.include_columns / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS include_pct_of_columns
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (23), CONVERT (MONEY, ((caMDKL.[index_columns] + caMDKL.include_columns) / sqCC.total_columns) * 100), 1)), 1, 23))
    		END) AS total_pct_of_columns
    	,CONVERT (VARCHAR (25), CONVERT (MONEY, (ISNULL (sqBAQ.individual_index_pages, 0) * 8) / 1024.0), 1) AS key_mb
    	,CONVERT (VARCHAR (6), CONVERT (DECIMAL (5, 2), ISNULL (((sqBAQ.individual_index_pages + .0) / sqBAQ.index_pages) * 100, 0))) AS key_mb_pct
    	,(CASE
    		WHEN sqBAQ.type_desc = N'HEAP' THEN ''
    		ELSE REVERSE (SUBSTRING (REVERSE (CONVERT (VARCHAR (25), CONVERT (MONEY, sqKL.total_max_length), 1)), 4, 22))
    		END) AS max_key_size_bytes
    	,(CASE
    		WHEN sqKL.total_max_length > 900 THEN 'Yes'
    		ELSE ''
    		END) AS over_key_size_limit
    	,ISNULL (ttIBKF.index_key, N'') AS index_key
    	,ISNULL (ttIBKF.include_key, N'') AS include_key
    	,ISNULL (ttIBKF.filter_definition, N'') AS filter_definition
    	,(CASE
    		WHEN sqED02.dupe_id IS NOT NULL THEN CONVERT (VARCHAR (20), sqED02.dupe_id) + ' - [' + CONVERT (VARCHAR (11), sqED02.total_dupes) + ']'
    		ELSE ''
    		END) AS dupe_id
    	,sqBAQ.is_unused AS unused
    	,ISNULL (CONVERT (VARCHAR (10), STATS_DATE (sqBAQ.[object_id], sqBAQ.index_id), 23), '') AS statistics_date
    	,(CASE
    		WHEN sqBAQ.[allow_row_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_row_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS row_locks
    	,(CASE
    		WHEN sqBAQ.[allow_page_locks] = 0 THEN 'No'
    		WHEN sqBAQ.[allow_page_locks] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS page_locks
    	,(CASE
    		WHEN sqBAQ.[ignore_dup_key] = 0 THEN 'No'
    		WHEN sqBAQ.[ignore_dup_key] = 1 THEN 'Yes'
    		ELSE ''
    		END) AS ignore_dupes
    	,(CASE
    		WHEN sqBAQ.no_recompute = 0 THEN 'Yes'
    		WHEN sqBAQ.no_recompute = 1 THEN 'No'
    		ELSE ''
    		END) AS auto_stats
    	,(CASE
    		WHEN sqBAQ.is_padded = 0 THEN 'No'
    		WHEN sqBAQ.is_padded = 1 THEN 'Yes'
    		ELSE ''
    		END) AS padded
    	,(CASE
    		WHEN sqBAQ.fill_factor = 0 THEN 100
    		ELSE sqBAQ.fill_factor
    		END) AS fill_factor
    	,(CASE
    		WHEN sqBAQ.user_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_seeks)
    		ELSE ''
    		END) AS user_seeks
    	,(CASE
    		WHEN sqBAQ.user_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_scans)
    		ELSE ''
    		END) AS user_scans
    	,(CASE
    		WHEN sqBAQ.user_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_lookups)
    		ELSE ''
    		END) AS user_lookups
    	,(CASE
    		WHEN sqBAQ.user_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.user_updates)
    		ELSE ''
    		END) AS user_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_seek, 23), '') AS last_user_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_scan, 23), '') AS last_user_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_lookup, 23), '') AS last_user_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_user_update, 23), '') AS last_user_update
    	,(CASE
    		WHEN sqBAQ.system_seeks > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_seeks)
    		ELSE ''
    		END) AS system_seeks
    	,(CASE
    		WHEN sqBAQ.system_scans > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_scans)
    		ELSE ''
    		END) AS system_scans
    	,(CASE
    		WHEN sqBAQ.system_lookups > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_lookups)
    		ELSE ''
    		END) AS system_lookups
    	,(CASE
    		WHEN sqBAQ.system_updates > 0 THEN CONVERT (VARCHAR (20), sqBAQ.system_updates)
    		ELSE ''
    		END) AS system_updates
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_seek, 23), '') AS last_system_seek
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_scan, 23), '') AS last_system_scan
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_lookup, 23), '') AS last_system_lookup
    	,ISNULL (CONVERT (VARCHAR (10), sqBAQ.last_system_update, 23), '') AS last_system_update
    FROM
    
    	(
    		SELECT
    			 O.[type]
    			,O.[schema_id]
    			,O.[object_id]
    			,CONVERT (VARCHAR (10), O.create_date, 23) AS create_date
    			,CONVERT (VARCHAR (10), O.modify_date, 23) AS modify_date
    			,sqDDPS.[rows]
    			,sqDDPS.total_pages
    			,sqDDPS.used_pages
    			,(CASE
    				WHEN sqDDPS.total_pages > sqDDPS.used_pages THEN sqDDPS.total_pages - sqDDPS.used_pages
    				ELSE 0
    				END) AS unused_pages
    			,sqDDPS.data_pages
    			,(CASE
    				WHEN sqDDPS.used_pages > sqDDPS.data_pages THEN sqDDPS.used_pages - sqDDPS.data_pages
    				ELSE 0
    				END) AS index_pages
    			,sqI.type_desc
    			,sqI.name AS index_name
    			,sqI.is_system_named
    			,sqI.is_primary_key
    			,sqI.is_unique
    			,sqI.is_disabled
    			,sqI.is_hypothetical
    			,sqI.individual_index_pages
    			,sqI.is_unused
    			,sqI.[allow_row_locks]
    			,sqI.[allow_page_locks]
    			,sqI.[ignore_dup_key]
    			,sqI.no_recompute
    			,sqI.is_padded
    			,sqI.fill_factor
    			,sqI.user_seeks
    			,sqI.user_scans
    			,sqI.user_lookups
    			,sqI.user_updates
    			,sqI.last_user_seek
    			,sqI.last_user_scan
    			,sqI.last_user_lookup
    			,sqI.last_user_update
    			,sqI.system_seeks
    			,sqI.system_scans
    			,sqI.system_lookups
    			,sqI.system_updates
    			,sqI.last_system_seek
    			,sqI.last_system_scan
    			,sqI.last_system_lookup
    			,sqI.last_system_update
    			,sqI.is_unique_constraint
    			,sqI.index_id
    			,sqI.row_filter
    		FROM
    			sys.objects O
    			INNER JOIN
    
    				(
    					SELECT
    						 ttIBSI.[object_id]
    						,SUM (ttIBSI.[rows]) AS [rows]
    						,SUM (ttIBSI.total_pages) AS total_pages
    						,SUM (ttIBSI.used_pages) AS used_pages
    						,SUM (ttIBSI.data_pages) AS data_pages
    					FROM
    						dbo.#temp_index_breakdown_size_info ttIBSI
    					GROUP BY
    						ttIBSI.[object_id]
    				) sqDDPS ON sqDDPS.[object_id] = O.[object_id]
    
    			INNER JOIN
    
    				(
    					SELECT
    						 I.[object_id]
    						,I.type_desc
    						,I.name
    						,KC.is_system_named
    						,I.is_primary_key
    						,I.is_unique
    						,I.is_disabled
    						,I.is_hypothetical
    						,(CASE
    							WHEN ttIBSI.used_pages > ttIBSI.data_pages THEN ttIBSI.used_pages - ttIBSI.data_pages
    							END) AS individual_index_pages
    						,(CASE
    							WHEN I.[type] = 0 THEN ''
    							WHEN I.[type] = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_primary_key = 1 THEN REPLICATE ('.', 6)
    							WHEN I.is_unique = 1 THEN REPLICATE ('.', 6)
    							WHEN EXISTS
    
    								(
    									SELECT
    										*
    									FROM
    										sys.index_columns IC
    										INNER JOIN sys.foreign_key_columns FKC ON FKC.parent_object_id = IC.[object_id]
    											AND FKC.parent_column_id = IC.column_id
    									WHERE
    										IC.[object_id] = I.[object_id]
    										AND IC.index_id = I.index_id
    								) THEN REPLICATE ('.', 6)
    
    							WHEN DDIUS.[object_id] IS NOT NULL THEN (CASE
    																		WHEN DDIUS.user_seeks + DDIUS.user_scans + DDIUS.user_lookups + DDIUS.user_updates = 0 THEN 'Y/N'
    																		ELSE 'No'
    																		END)
    							ELSE 'Yes'
    							END) AS is_unused
    						,I.[allow_row_locks]
    						,I.[allow_page_locks]
    						,I.[ignore_dup_key]
    						,S.no_recompute
    						,I.is_padded
    						,I.fill_factor
    						,DDIUS.user_seeks
    						,DDIUS.user_scans
    						,DDIUS.user_lookups
    						,DDIUS.user_updates
    						,DDIUS.last_user_seek
    						,DDIUS.last_user_scan
    						,DDIUS.last_user_lookup
    						,DDIUS.last_user_update
    						,DDIUS.system_seeks
    						,DDIUS.system_scans
    						,DDIUS.system_lookups
    						,DDIUS.system_updates
    						,DDIUS.last_system_seek
    						,DDIUS.last_system_scan
    						,DDIUS.last_system_lookup
    						,DDIUS.last_system_update
    						,I.is_unique_constraint
    						,I.index_id
    						,ROW_NUMBER () OVER
    											(
    												PARTITION BY
    													I.[object_id]
    												ORDER BY
    													 I.is_primary_key DESC
    													,(CASE
    														WHEN I.[type] = 0 THEN 'Z'
    														ELSE 'A'
    														END)
    													,I.[type]
    													,I.name
    											) AS row_filter
    					FROM
    						sys.indexes I
    						INNER JOIN dbo.#temp_index_breakdown_size_info ttIBSI ON ttIBSI.[object_id] = I.[object_id]
    							AND ttIBSI.index_id = I.index_id
    						LEFT JOIN sys.key_constraints KC ON KC.parent_object_id = I.[object_id]
    							AND KC.unique_index_id = I.index_id
    						LEFT JOIN sys.stats S ON S.[object_id] = I.[object_id]
    							AND S.stats_id = I.index_id
    						LEFT JOIN master.sys.dm_db_index_usage_stats DDIUS ON DDIUS.[object_id] = I.[object_id]
    							AND DDIUS.index_id = I.index_id
    							AND DDIUS.database_id = @Database_ID
    				) sqI ON sqI.[object_id] = O.[object_id]
    
    		WHERE
    			O.[type] IN ('U', 'V')
    			AND O.is_ms_shipped = 0
    			AND NOT
    
    				(
    					SCHEMA_NAME (O.[schema_id]) = N'dbo'
    					AND O.name = N'sysdiagrams'
    					AND O.[type] = 'U'
    				)
    
    	) sqBAQ
    
    	INNER JOIN
    
    		(
    			SELECT
    				 C.[object_id]
    				,COUNT (*) + .0 AS total_columns
    			FROM
    				sys.columns C
    			GROUP BY
    				C.[object_id]
    		) sqCC ON sqCC.[object_id] = sqBAQ.[object_id]
    
    	LEFT JOIN dbo.#temp_index_breakdown_keys_filters ttIBKF ON ttIBKF.[object_id] = sqBAQ.[object_id]
    		AND ttIBKF.index_id = sqBAQ.index_id
    	LEFT JOIN
    
    		(
    			SELECT
    				 IC.[object_id]
    				,IC.index_id
    				,SUM (C.max_length) AS total_max_length
    			FROM
    				sys.index_columns IC
    				INNER JOIN sys.columns C ON C.[object_id] = IC.[object_id]
    					AND C.column_id = IC.column_id
    			WHERE
    				IC.is_included_column = 0
    			GROUP BY
    				 IC.[object_id]
    				,IC.index_id
    		) sqKL ON sqKL.[object_id] = sqBAQ.[object_id] AND sqKL.index_id = sqBAQ.index_id
    
    	LEFT JOIN
    
    		(
    			SELECT
    				 sqED01.dupe_rank
    				,sqED01.total_dupes
    				,ROW_NUMBER () OVER
    									(
    										ORDER BY
    											(SELECT NULL)
    									) AS dupe_id
    			FROM
    
    				(
    					SELECT
    						 ttIBKF.dupe_rank
    						,COUNT (*) AS total_dupes
    					FROM
    						dbo.#temp_index_breakdown_keys_filters ttIBKF
    					GROUP BY
    						ttIBKF.dupe_rank
    					HAVING
    						COUNT (*) > 1
    				) sqED01
    
    		) sqED02 ON sqED02.dupe_rank = ttIBKF.dupe_rank
    
    	CROSS APPLY
    
    		(
    			SELECT
    				 (CASE
    					WHEN sqBAQ.modify_date = sqBAQ.create_date THEN REPLICATE ('.', 18)
    					ELSE sqBAQ.modify_date
    					END) AS modify_date
    				,LEN (ttIBKF.index_key) - LEN (REPLACE (ttIBKF.index_key, '•', '')) AS [index_columns]
    				,ISNULL (LEN (ttIBKF.include_key) - LEN (REPLACE (ttIBKF.include_key, '•', '')), 0) AS include_columns
    		) caMDKL
    
    ORDER BY
    	 sqBAQ.[type]
    	,SCHEMA_NAME (sqBAQ.[schema_id])
    	,OBJECT_NAME (sqBAQ.[object_id])
    	,sqBAQ.row_filter
    
    
    -----------------------------------------------------------------------------------------------------------------------------
    --	Cleanup: Drop Any Remaining Temp Tables
    -----------------------------------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_keys_filters', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_keys_filters
    
    END
    
    
    IF OBJECT_ID (N'tempdb.dbo.#temp_index_breakdown_size_info', N'U') IS NOT NULL
    BEGIN
    
    	DROP TABLE dbo.#temp_index_breakdown_size_info
    
    END

    public by sjschmalfeld  298721  1  4  1

    Kill Connections to a Database SQL Server

    sql
    // KILL CONNECTIONS TO A DATABASE
    
    USE [master]
    GO  
    
    DECLARE @kill varchar(8000) = '';  
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('MyDB')
    
    EXEC(@kill);

    public by lbottaro  303309  31  7  0

    How to read and parse a txt file in python

    This code shows how to read a txt file and parse the data
    import os
    import sys
    
    myfile = open('/some/path/to/file.txt', "r")
    # EXAMPLE OF FILE FORMAT:
    # abc 123 234 456 678
    # def 122223 2333334 454446 611178
    # ghi 144423 211134 44456 61114478
    
    for line in myfile.readlines():
        name= ''
        numList = list()
    
        print line
        try:
            name = line.split()[0] # it will get 'abc'/'def'/'ghi'
            numList = line.split()[1:] # it will get all other stuff inline...
        except:
            print "Invalid file format"
            myfile.close()
            sys.exit(1)
        
        if not numList:
            print("Invalid numList format! name= " + str(name))
            myfile.close()
            sys.exit(1)
    
        print 'name='+str(name)
        print 'numList='+str(numList)
    
    myfile.close()
    • Public Snippets
    • Channels Snippets