SQL Queries to find Values or else from Database

-

Query to Find Column From All Tables of Database

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

 

Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

How to search every table and field in a SQL Server Database

CREATE PROC SearchAllTables (     @SearchStr nvarchar(100) ) AS

CollapseBEGIN     -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.     -- Purpose: To search all columns of all tables for a given search string     -- Written by: Narayana Vyas Kondreddi     -- Site: http://vyaskn.tripod.com     -- Tested on: SQL Server 7.0 and SQL Server 2000     -- Date modified: 28th July 2002 22:50 GMT     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))     SET NOCOUNT ON     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)     SET  @TableName = ''     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')     WHILE @TableName IS NOT NULL
    BEGIN         SET @ColumnName = ''         SET @TableName =          (             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))             FROM     INFORMATION_SCHEMA.TABLES             WHERE         TABLE_TYPE = 'BASE TABLE'                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName                 AND    OBJECTPROPERTY(                         OBJECT_ID(                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)                              ), 'IsMSShipped'                                ) = 0         )         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN             SET @ColumnName =             (                 SELECT MIN(QUOTENAME(COLUMN_NAME))                 FROM     INFORMATION_SCHEMA.COLUMNS                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName             )                  IF @ColumnName IS NOT NULL
            BEGIN                 INSERT INTO #Results                 EXEC                 (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)                      FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2                 )             END
        END    
    END
    SELECT ColumnName, ColumnValue FROM #Results END
 

Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

 

 

 


 

 

Comments

Post a Comment

Popular posts from this blog

what is Event Cache table in sharepoint

-
Every Content database in SharePoint Contains an EventCache table that is the change log for objects contained in the database. Each row in this table is depicting a change in an object where columns usually contain information such as the date and time of a change, the type of object that was changed, the nature of the change, and a unique identifier for the object. EventCache table is important because it stores the list of site events for which users might have requested alerts.
Read more

CAML Query syntax and options in SharePoint

-
As Small syntax error or order of tag in spquery can result any web part (or any thing in which spquery is used) doesn’t work, I’m explaining SPQuery with example, hope which may help you. CAML (Collaborative Application Markup Language) SPQuery  is the SharePoint object which is used to perform a query operation against SharePoint data. SPList.getItems(SPQuery)  is the step, will return SPListItemCollection  which satisfies the query.SPQuery has one data member ‘ Query’ , which need to set before passing SPQuery object to  SPList . Some Properties Properties Description RowLimit Gets or sets a limit for the number of items returned in the query per page. <RowLimit>10</RowLimit> --- Can be used as: query.rowLimit = 10; ViewFields Gets or sets the fields that are displayed in the query. <ViewFields>         <FieldRef Name='Title'/>         <...
Read more

SharePoint 2013 boundaries and limits

-
Image
This Article was founded at   Sharepoint 2013 In this article: Limits by hierarchy This section provides limits sorted by the logical hierarchy of a SharePoint Server 2013 farm. Web application limits The following table lists the recommended guidelines for web applications.   Limit Maximum value Limit type Notes Web application 20 per farm Supported We recommended limiting the number of web applications as much as possible. Create additional host named site collections where possible instead of adding web applications. Zone 5 per web application Boundary The number of zones defined for a farm is hard-coded to 5. Zones include Default, Intranet, Extranet, Internet, and custom. Managed path for host-named site collections 20 per farm Supported Managed paths for host-named site collections apply at the farm level. Each managed path that is created can be applied in any Web application...
Read more