Querying the SQL Server System Catalog FAQ

How do I find all the tables that do not have a clustered index in a specified database?

Before you run the following queries, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.tables AS t
WHERE NOT EXISTS 
   (
     SELECT * FROM sys.indexes AS i
     WHERE i.object_id = t.object_id
     AND i.type = 1  -- or type_desc = 'CLUSTERED'
   )
ORDER BY schema_name, table_name;
GO

Or, you can use the OBJECTPROPERTY function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0
ORDER BY schema_id, name;
GO

TOP

How do I find all the owners of entities contained in a specified schema?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = ''
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '';
GO

TOP

How do I find all the tables that do not have a primary key?

Before you run the following queries, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
    ,t.name AS table_name
FROM sys.tables t 
WHERE object_id NOT IN 
   (
    SELECT parent_object_id 
    FROM sys.key_constraints 
    WHERE type_desc = 'PRIMARY_KEY_CONSTRAINT' -- or type = 'PK'
    );
GO

Or, you can run the following query.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY schema_name, table_name;
GO

TOP

How do I find all the tables that do not have an index?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

TOP

How do I find all the tables that have an identity column?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

Or, you can run the following query.

Note
This query does not return the name of the columns.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,name AS table_name 
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
ORDER BY schema_name, table_name;
GO

TOP

How do I find the data types of the columns of a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('')
ORDER BY c.column_id;
GO

TOP

How do I find the dependencies on a specified function?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS referencing_object_name
    ,COALESCE(COL_NAME(object_id, column_id), '(n/a)') AS referencing_column_name
    ,*
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO 

TOP

How do I find all the stored procedures in a database?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT name AS procedure_name 
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,type_desc
    ,create_date
    ,modify_date
FROM sys.procedures;
GO

TOP

How do I find the parameters for a specified stored procedure or function?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

TOP

How do I find all the user-defined functions in a database?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT name AS function_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

TOP

How do I find all views in a database?

Before you run the following query, replace  with a valid database name.

Transact-SQL

USE ;
GO
SELECT name AS view_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,OBJECTPROPERTYEX(object_id,'IsIndexed') AS IsIndexed
  ,OBJECTPROPERTYEX(object_id,'IsIndexable') AS IsIndexable
  ,create_date
  ,modify_date
FROM sys.views;
GO

TOP

How do I find all the entities that have been modified in the last N days?

Before you run the following query, replace  and  with valid values.

Transact-SQL

USE ;
GO
SELECT name AS object_name 
  ,SCHEMA_NAME(schema_id) AS schema_name
  ,type_desc
  ,create_date
  ,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 
ORDER BY modify_date;
GO

TOP

How do I find the LOB data types of a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT name AS column_name 
    ,column_id 
    ,TYPE_NAME(user_type_id) AS type_name
    ,max_length
    ,CASE 
       WHEN max_length = -1 AND TYPE_NAME(user_type_id) <> 'xml'
            THEN 1
            ELSE 0
     END AS [(max)]
FROM sys.columns
WHERE object_id=OBJECT_ID('') 
    AND ( TYPE_NAME(user_type_id) IN ('xml','text', 'ntext','image')
         OR (TYPE_NAME(user_type_id) IN ('varchar','nvarchar','varbinary')
         AND max_length = -1)
        );
GO

TOP

How do I view the definition of a module?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('');
GO

Or, you can use the OBJECT_DEFINITION function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('')) AS ObjectDefinition;
GO

TOP

How do I view the definition of a server-level trigger?

Transact-SQL

SELECT definition
FROM sys.server_sql_modules;
GO

TOP

How do I find the columns of a primary key for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,ic.index_column_id
    ,key_ordinal
    ,c.name AS column_name
    ,TYPE_NAME(c.user_type_id)AS column_type 
    ,is_identity
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c 
    ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('');
GO

Or, you can use the COL_NAME function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,COL_NAME(ic.object_id,ic.column_id) AS column_name
    ,ic.index_column_id
    ,key_ordinal
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic 
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1 
    AND i.object_id = OBJECT_ID('');
GO

TOP

How do I find the columns of a foreign key for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('');

TOP

How do I find the permissions granted or denied to a specified principal?

The following example creates a function to return the name of the entity on which the permissions are checked. The function is invoked in the queries that follow. The function must be created in every database in which you want to check permissions.

Transact-SQL

-- Create a function to return the name of the entity on which the permissions are checked.
IF OBJECT_ID (N'dbo.entity_instance_name', N'FN') IS NOT NULL
    DROP FUNCTION dbo.entity_instance_name;
GO
CREATE FUNCTION dbo.entity_instance_name(@class_desc nvarchar(60), @major_id int) 
RETURNS sysname AS
BEGIN
    DECLARE @the_entity_name sysname
    SELECT @the_entity_name = CASE
        WHEN @class_desc = 'DATABASE' THEN DB_NAME()
        WHEN @class_desc = 'SCHEMA' THEN SCHEMA_NAME(@major_id)
        WHEN @class_desc = 'OBJECT_OR_COLUMN' THEN OBJECT_NAME(@major_id)
        WHEN @class_desc = 'DATABASE_PRINCIPAL' THEN USER_NAME(@major_id)
        WHEN @class_desc = 'ASSEMBLY' THEN 
            (SELECT name FROM sys.assemblies WHERE assembly_id=@major_id)
        WHEN @class_desc = 'TYPE' THEN TYPE_NAME(@major_id)
        WHEN @class_desc = 'XML_SCHEMA_COLLECTION' THEN 
            (SELECT name FROM sys.xml_schema_collections
              WHERE xml_collection_id=@major_id)
        WHEN @class_desc = 'MESSAGE_TYPE' THEN 
            (SELECT name FROM sys.service_message_types WHERE message_type_id=@major_id)
        WHEN @class_desc = 'SERVICE_CONTRACT' THEN 
           (SELECT name FROM sys.service_contracts
              WHERE service_contract_id=@major_id)
        WHEN @class_desc = 'SERVICE' THEN
          (SELECT name FROM sys.services WHERE service_id=@major_id)
        WHEN @class_desc = 'REMOTE_SERVICE_BINDING' THEN
          (SELECT name FROM sys.remote_service_bindings
             WHERE remote_service_binding_id=@major_id)
        WHEN @class_desc = 'ROUTE' THEN
          (SELECT name FROM sys.routes WHERE route_id=@major_id)
        WHEN @class_desc = 'FULLTEXT_CATALOG' THEN
          (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id=@major_id)
        WHEN @class_desc = 'SYMMETRIC_KEY' THEN
          (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id=@major_id)
        WHEN @class_desc = 'CERTIFICATE' THEN
          (SELECT name FROM sys.certificates WHERE certificate_id=@major_id)
        WHEN @class_desc = 'ASYMMETRIC_KEY' THEN
          (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id=@major_id)
        WHEN @class_desc = 'SERVER' THEN 
             (SELECT name FROM sys.servers WHERE server_id=@major_id)
        WHEN @class_desc = 'SERVER_PRINCIPAL' THEN SUSER_NAME(@major_id)
        WHEN @class_desc = 'ENDPOINT' THEN 
             (SELECT name FROM sys.endpoints WHERE endpoint_id=@major_id)      
        ELSE '?'
    END
    RETURN @the_entity_name
END;
GO
-- Return server-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc, major_id) AS entity_name 
    ,minor_id
    ,SUSER_NAME(grantee_principal_id) AS grantee
    ,SUSER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc 
FROM sys.server_permissions 
WHERE grantee_principal_id = SUSER_ID('public');
GO
-- Return database-level permissions for the user.
SELECT class
    ,class_desc
    ,dbo.entity_instance_name(class_desc , major_id) AS entity_name 
    ,minor_id
    ,USER_NAME(grantee_principal_id) AS grantee
    ,USER_NAME(grantor_principal_id) AS grantor
    ,type
    ,permission_name
    ,state_desc   
FROM  sys.database_permissions 
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('public');
GO

TOP

How do I determine if a column is used in a computed column expression?

Before you run the following query, replace , , and > with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS computed_column 
    ,class_desc
    ,is_selected
    ,is_updated
    ,is_select_all
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('')
    AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '', 'ColumnId')
    AND class = 1;
GO

TOP

How do I find all the columns that are used in a computed column expression?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(d.referenced_major_id) AS object_name
    ,COL_NAME(d.referenced_major_id, d.referenced_minor_id) AS column_name
    ,OBJECT_NAME(referenced_major_id) AS dependent_object_name 
    ,COL_NAME(d.object_id, d.column_id) AS dependent_computed_column
    ,cc.definition AS computed_column_definition
FROM sys.sql_dependencies AS d
JOIN sys.computed_columns AS cc 
    ON cc.object_id = d.object_id AND cc.column_id = d.column_id AND d.object_id=d.referenced_major_id     
WHERE d.class = 1
ORDER BY object_name, column_name;
GO

TOP

How do I find the columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and _type_name> with a valid, schema-qualified CLR user-defined type, or schema-qualified alias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,c.name AS column_name 
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,TYPE_NAME(c.user_type_id) AS user_type_name
    ,c.max_length
    ,c.precision
    ,c.scale
    ,c.is_nullable
    ,c.is_computed
FROM sys.columns AS c
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE c.user_type_id = TYPE_ID(''); 
GO

The following query returns a restricted and narrow view of columns dependent on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name 
    ,COL_NAME(object_id, column_id) AS column_name
    ,TYPE_NAME(user_type_id) AS user_type
FROM sys.column_type_usages
WHERE user_type_id = TYPE_ID('');
GO

TOP

How do I find the computed columns that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and  with a valid, schema-qualified CLR user-defined type, alias type name.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,COL_NAME(object_id, column_id) AS column_name
FROM sys.sql_dependencies
WHERE referenced_major_id = TYPE_ID('')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(object_id, 'IsTable') = 1;   -- exclude non-table dependencies 

TOP

How do I find the parameters that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and _name.data_type_name> with a valid, schema-qualified CLR user-defined type, alias type name. The following query requires membership in the db_owner role or permissions to see all dependent column and computed column metadata in the database.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,NULL AS procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('')
UNION 
SELECT OBJECT_NAME(object_id) AS object_name
    ,procedure_number
    ,name AS param_name
    ,parameter_id AS param_num
    ,TYPE_NAME(p.user_TYPE_ID) AS type_name
FROM sys.numbered_procedure_parameters AS p
WHERE p.user_TYPE_ID = TYPE_ID('')
ORDER BY object_name, procedure_number, param_num;
GO

The following query returns a restricted and narrow view of parameters that depend on a CLR user-defined type or alias, but the result set is visible to the public role. You can use this query if you have granted REFERENCE permissions on your user-defined type to others and you do not have permission to view the metadata of the objects others have created that use the type.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) AS object_name
    ,parameter_id
    ,TYPE_NAME(user_type_id) AS type_name
FROM sys.parameter_type_usages 
WHERE user_type_id = TYPE_ID('');
GO

TOP

How do I find the CHECK constraints that depend on a specified CLR user-defined type?

Before you run the following query, replace  with a valid name and _name.data_type_name> with a valid, schema-qualified CLR user-defined type name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(o.parent_object_id) AS table_name
    ,OBJECT_NAME(o.object_id) AS constraint_name
FROM sys.sql_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.object_id
WHERE referenced_major_id = TYPE_ID('')
    AND class = 2 -- schema-bound references to type
    AND OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1; -- exclude non-CHECK dependencies
GO

TOP

How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?

Before you run the following query, replace  with a valid name and  with a valid, schema-qualified CLR user-defined type, alias type name.

The parameters defined in a function or procedure are implicitly schema bound. Therefore, parameters that depend on a CLR user-defined type or alias type can be viewed by using the sys.sql_dependenciescatalog view. Procedures and triggers are not schema bound. This means that dependencies between any expression defined in the body of the procedure or trigger and a CLR user-defined type or alias type is not maintained. Schema bound views and schema bound user-defined functions that have expressions that depend on a CLR user-defined type or alias type are maintained in the sys.sql_dependencies catalog view. Dependencies between types and CLR functions and CLR procedures are not maintained.

The following query returns all schema-bound dependencies in views, Transact-SQL functions, and Transact-SQL stored procedures for a specified CLR user-defined type or alias type.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS dependent_object_schema
  ,OBJECT_NAME(o.object_id) AS dependent_object_name
  ,o.type_desc AS dependent_object_type
  ,d.class_desc AS kind_of_dependency
  ,TYPE_NAME (d.referenced_major_id) AS type_name
FROM sys.sql_dependencies AS d 
JOIN sys.objects AS o
  ON d.object_id = o.object_id
  AND o.type IN ('FN','IF','TF', 'V', 'P')
WHERE d.class = 2 -- dependencies on types
  AND d.referenced_major_id = TYPE_ID('')
ORDER BY dependent_object_schema, dependent_object_name;
GO

TOP

How do I find all the constraints for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id) as constraint_name
    ,SCHEMA_NAME(schema_id) AS schema_name
    ,OBJECT_NAME(parent_object_id) AS table_name
    ,type_desc
    ,create_date
    ,modify_date
    ,is_ms_shipped
    ,is_published
    ,is_schema_published
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT' 
    AND parent_object_id = OBJECT_ID('');
GO

TOP

How do I find all the indexes for a specified table?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('');
GO

TOP

How do I find all the objects that have a specified column name?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT OBJECT_NAME(object_id)
FROM sys.columns
WHERE name = '';
GO

Or

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name 
    ,o.name AS object_name
    ,type_desc
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
WHERE c.name = '';
GO

TOP

How do I find all the user-defined tables in a specified database?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT * 
FROM sys.tables;
GO

TOP

How do I find all the tables and indexes that are partitioned?

Before you run the following query, replace  with a valid name.

Transact-SQL

USE ;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name
    ,OBJECT_NAME(p.object_id) AS table_name
    ,i.name AS index_name
    ,p.partition_number
    ,rows 
FROM sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
INNER JOIN sys.objects AS o ON o.object_id = i.object_id
ORDER BY index_name, partition_number;
GO

TOP

How do I find all the statistics on a specified object?

Before you run the following query, replace  with a valid name and  with a valid table, indexed view, or table-valued function name.

Transact-SQL

USE ;
GO
SELECT name AS statistics_name
    ,stats_id
    ,auto_created
    ,user_created
    ,no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('');
GO

TOP

How do I find all the statistics and statistics columns on a specified object?

Before you run the following query, replace  with a valid name and  with a valid table, indexed view, or table-valued function name.

Transact-SQL

USE ;
GO
SELECT s.name AS statistics_name
    ,c.name AS column_name
    ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc 
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c 
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID('');
GO

TOP

How do I find the definition of a view?

Before you run the following query, replace  and  with valid names.

Transact-SQL

USE ;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('');
GO

Or, you can use the OBJECT_DEFINITION function as shown in the following example.

Transact-SQL

USE ;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('')) AS ObjectDefinition;
GO

TOPhttp://msdn.microsoft.com/en-us/library/ms345522.aspx

View / Grant / Revoke Security permissions

--view security definitions
select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
     sys.database_permissions.permission_name,
     sys.database_permissions.state permission_state,
     sys.database_permissions.state_desc,
     state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
 from sys.database_permissions
 join sys.objects on sys.database_permissions.major_id =
     sys.objects.object_id
 join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
 join sys.database_principals on sys.database_permissions.grantee_principal_id =
     sys.database_principals.principal_id
 order by 1, 2, 3, 5

--revoke permission
select
'USE ['+DB_NAME()+']' + ' REVOKE VIEW DEFINITION ON [dbo].['+sys.objects.name+'] FROM ['+sys.database_principals.name+']'
,*
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where permission_name = 'VIEW DEFINITION'
and sys.database_principals.name = 'CSODMGMT\SQLAllDBAccess'

SQL Server Service Broker example on how to configure, send and receive messages

SQL Server Service Broker example on how to configure, send and receive messages

Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

New columnstore index feature in SQL Server 2012

New columnstore index feature in SQL Server 2012

SQL Jobs with Multiple schedules

use msdb
go
/*
select * from sysjobs
select * from sysjobschedules
*/

select a.job_id , a.name,  COUNT(*)
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1



select c.name, b.*, a.*
from sysschedules a
inner join sysjobschedules b on a.schedule_id = b.schedule_id
inner join sysjobs c on c.job_id = b.job_id
where a.schedule_id in
(
select schedule_id from sysjobschedules
where job_id in
(
select a.job_id
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1
)
)

Verify SQL Server Stored Procedures are the same on multiple servers

Verify SQL Server Stored Procedures are the same on multiple servers

Long Running Queries Alert

use master
go

/*
exec sp_long_running_queries @longduration = 15 , @sendalertemail = 1
*/
ALTER procedure sp_long_running_queries
(
@longduration int = 15 -- minutes
, @sendalertemail int = 0
)
as
BEGIN

CREATE TABLE #_tmp_whoisactive
( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

exec sp_WhoIsActive @destination_table = '#_tmp_whoisactive'

--select datediff(mi,start_time,collection_time) , * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration

if @sendalertemail = 1 and exists ( select * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration)
begin

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
N'

Long Runing Queries on '+@@SERVERNAME+' executing over '+convert(nvarchar(10),@longduration)+' minutes

' +
N'
' +
N'
' +
N'
' +
N'
' +
CAST ( ( SELECT td = [dd hh:mm:ss.mss],       '',
td = session_id, '',
td = login_name, '',
td = database_name, '',
td = host_name, '',
td = Status
 FROM #_tmp_whoisactive
 where datediff(mi,start_time,collection_time) > @longduration
 FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'

Time Elapsed	Session	Login Name	Database	Executing Host	Status

' ;

--print @tableHTML
 
set @subjectstring = 'Long Running Queries over '+convert(nvarchar(10),@longduration)+' min on SQL Server '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'DLProductionDBA@cornerstoneondemand.com;jferris@csod.com',
--@recipients ='palexander@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
 
end

drop table #_tmp_whoisactive

END
GO

If Primary Key Exists

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo'

if not exists(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo' )

begin

ALTER TABLE [dbo].[user_career_pref_date]

    ADD CONSTRAINT [PK_user_career_pref_date] PRIMARY KEY CLUSTERED ([user_id] ASC, [question_id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

end

Proc to show expensive queries : sp_expensive_queries

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_expensive_queries]
as
BEGIN


SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

SELECT TOP 10
[Total Logical Reads] = qs.total_logical_reads,
[Last Logical Reads] = qs.last_logical_reads,
[Total Logical Writes] = qs.total_logical_writes,
[Last Logical Writes] = qs.last_logical_writes,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid),
[Total Worker Time] = qs.total_worker_time,
[Last Worker Time] = qs.last_worker_time,
[Total Elapsed Time Seconds] = qs.total_elapsed_time/1000000 ,
[Last Elapsed Time Seconds] = qs.last_elapsed_time/1000000 ,
[Last Execution Time] = qs.last_execution_time,
[Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

END

Automate Monitoring SQL Server Error Logs with Email Alerts

Automate Monitoring SQL Server Error Logs with Email Alerts

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Script to configure SQL database Mail

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure 'Database Mail XPs','1'
RECONFIGURE WITH OVERRIDE;
GO

use msdb
go
exec sysmail_start_sp

/*
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'default' ;
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'default' ;
*/  
 
DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
   @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'default';

-- Account information. Replace with the information for your account.

SET @account_name = 'default';
SET @SMTP_servername = '10.11.150.25';
SET @email_address = REPLACE(@@servername,'-','')+'@csod.com';
        SET @display_name = REPLACE(@@servername,'-','');


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile () already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account () already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account ().', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile ().', 16, 1);
ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the speficied profile with the specified account ().', 16, 1) ;
ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'default',
    @is_default = 1 ;
GO


EXEC msdb.dbo.sp_send_dbmail @recipients = 'palexander@csod.com', @subject='test', @body='test', @body_format = 'TEXT'

/*

http://technet.microsoft.com/en-us/library/ms177580(v=sql.90).aspx

*/

sp_job_steps_history : Job History for Steps

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_job_steps_history]    Script Date: 02/08/2012 08:13:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_job_steps_history]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_job_steps_history]
GO


/*
--all failed steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = null

--all failed specific steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = 15

--outcome of the specific step for all jobs all dates
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = null , @stepid = 15

--outcome of the specific step for all jobs specific date
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = null , @stepid = 15

--all failed steps for given day
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given day specific step
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = 13

--all failed steps for given job specific date
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given job all dates
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = null, @runstatus = 0 , @stepid = null

--specific job status for given dae
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = null , @stepid = null

*/
CREATE PROCEDURE dbo.sp_job_steps_history
(
@jobname nvarchar(256) = null
,@rundate int = null
,@runstatus int = 0
,@stepid int = null
)
as
BEGIN
/*
@runstatus:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
*/

/*
set @jobname = null
set @rundate = null
set @runstatus = 3
*/

SELECT j.name JobName,h.step_id, h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where (j.name like @jobname or @jobname is null)
and (h.run_date = @rundate or @rundate is null)
and (h.run_status = @runstatus or @runstatus is null)
and (h.step_id = @stepid or @stepid is null)
ORDER BY j.name, h.run_date, h.run_time

END
GO

What's the Point of Using VARCHAR(n) Anymore?

What's the Point of Using VARCHAR(n) Anymore?

SQL Orphan users

select u.name , 'exec sp_revokedbaccess '''+u.name+''''
from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole <> 1 and isapprole <> 1  
    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
    and u.name <> 'system_function_schema')
   

Win 2008 Run As

Create a batch file that has the following on SQL server:

cd\
cd C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE
runas /user:csodmgmt\palexander ssms

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Active Directory from SQL

Configuration on Server

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'[10.10.10.10]'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\user',@rmtpassword='password'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO



select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
') AS tblADSI
ORDER BY SAMAccountName


select * from openquery
(
ADSI,'SELECT NAME,SAMAccountName,displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
FROM ''LDAP://10.10.10.10''
WHERE objectCategory = ''Person''
AND objectClass = ''user''
AND sAMAccountName = ''username''
') AS tblADSI
ORDER BY displayname




Find groups and users all together



-- Configure AD Domain to search

DECLARE @strDC NVARCHAR(100)
SET @strDC = 'dc=csodmgmt,dc=corp'

-- Declare Variables

DECLARE @chvAlphaChars VARCHAR(60),
@chvSearch VARCHAR(10),
@chvSearchLevel1 VARCHAR(1),
@chvSearchLevel2 VARCHAR(1),
@chvSearchLevel3 VARCHAR(1),
@intcountLevel1 INT,
@intcountLevel2 INT,
@intcountLevel3 INT,
@intRowCount INT,
@strSQL NVARCHAR(4000),
@strADSISQL NVARCHAR(4000),
@Login VARBINARY(85),
@CN VARCHAR(512),
@CT INT,
@ADRoot VARCHAR(255)


-- Declare temp tables

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Users'))
DROP TABLE #LDAP_AD_V2_USERS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERS] (
[ROW_ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[SID] [VARBINARY](85) NULL,
[SAMACCOUNTNAME] [NVARCHAR](256) NULL,
[CN] [NVARCHAR](256) NULL,
[SN] [NVARCHAR](256) NULL,
[DISPLAYNAME] [NVARCHAR](256) NULL,
[GIVENNAME] [NVARCHAR](256) NULL,
[TELEPHONENUMBER] [NVARCHAR](256) NULL,
[ADSPATH] [NVARCHAR](256) NULL,
[HOMEDIRECTORY] [NVARCHAR](256) NULL,
[MAIL] [NVARCHAR](256) NULL,
[MEMBEROF] [NVARCHAR](256) NULL,
[PRIMARYGROUPID] [INT],
[CREATETIMESTAMP] DATETIME)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_Groups'))
DROP TABLE #LDAP_AD_V2_GROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_GROUPS] (
[ID] [INT] IDENTITY ( 1,1 ) NOT NULL,
[LOGIN] [VARCHAR](512) NULL,
[EMAIL] [VARCHAR](255) NULL,
[ACCTNAME] [VARCHAR](512) NULL,
[DISTNAME] [VARCHAR](512) NULL,
[CREATEDDATE] [DATETIME] NULL,
[CHANGEDDATE] [DATETIME] NULL,
[MGR] [VARCHAR](512) NULL,
[SID] [VARBINARY](85) NULL)
ON [PRIMARY]

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#LDAP_AD_V2_UserGroups'))
DROP TABLE #LDAP_AD_V2_USERGROUPS

CREATE TABLE [DBO].[#LDAP_AD_V2_USERGROUPS] (
[USERLOGIN] [VARBINARY](85) NOT NULL,
[GROUPLOGIN] [VARBINARY](85) NULL)
ON [PRIMARY]

-- POPULATION OF USERS

-- Search letters to cycle through
-- any chars, but the first char must be a space
SET @chvAlphaChars = ' ABCDEFGHIJKLMNOPQRSTUVWXYZ.-_`0123456789'
SET NOCOUNT ON

-- start on non space char
SET @intcountLevel1 = 2
-- first level loop
WHILE @intcountLevel1 <= LEN(@chvAlphaChars)
BEGIN
-- get first level char
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
-- reset start on space
SET @intcountLevel2 = 1
-- second level loop
WHILE @intcountLevel2 <= LEN(@chvAlphaChars)
BEGIN
-- reset start on space
SET @intcountLevel3 = 1
-- third level loop
WHILE @intcountLevel3 <= LEN(@chvAlphaChars)
BEGIN
-- setup the string to search for. By using the trim function we can form each level depending on no records
-- eg A 99, B 1000 > BA 9, BB 20 etc
-- trim the spaces forming just A, B, C ; AA, AB for search etc
SET @chvSearchLevel1 = SUBSTRING(@chvAlphaChars,@intcountLevel1,1)
SET @chvSearchLevel2 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel2,1))
SET @chvSearchLevel3 = RTRIM(SUBSTRING(@chvAlphaChars,@intcountLevel3,1))
SET @chvSearch = @chvSearchLevel1 + @chvSearchLevel2 + @chvSearchLevel3
SET @strADSISQL = 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'where objectCategory = ''''Person'''' '
+ CHAR(13) + 'and objectClass = ''''user'''' '
+ CHAR(13) + 'and sAMAccountName = ''''' + @chvSearch + '*'''' '
SET @strSQL = 'insert into #LDAP_AD_V2_Users (sid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp) '
+ CHAR(13) + 'select objectSid,cn,sn,displayName, sAMAccountName, givenName, telephoneNumber, adspath, homedirectory, mail, primarygroupid, createTimeStamp '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

EXEC SP_EXECUTESQL @strSQL

SET @intRowCount = @@ROWCOUNT

-- prints what string is being searched for : no of inserts
-- PRINT @chvSearch + ' : ' + CONVERT(VARCHAR,@intRowCount)

-- if searched on @chvSearchLevel1 and under 1000 then everything is fine so skip search2 to next search1 eg A > B
IF @intRowCount < 1000
AND @chvSearchLevel2 = ''
SET @intcountLevel2 = @intcountLevel2 + 100

-- if searched on @chvSearchLevel2 and under 1000 then everything is fine so skip to next search2 eg AA > AB
IF @intRowCount < 1000
AND @chvSearchLevel3 = ''
SET @intcountLevel3 = @intcountLevel3 + 100

-- else over 1000 so increment third level
SET @intcountLevel3 = @intcountLevel3 + 1
END

-- increment next second level char
SET @intcountLevel2 = @intcountLevel2 + 1
END

-- increment next first level char
SET @intcountLevel1 = @intcountLevel1 + 1
END


-- POPULATION OF GROUPS

SET @strADSISQL = 'select objectSid, managedBy, whenChanged, whenCreated, distinguishedName, name, samAccountName, mail '
+ CHAR(13) + 'FROM ''''LDAP://' + @strDC + ''''' '
+ CHAR(13) + 'WHERE objectCategory = ''''Group'''' '

SET @strSQL = 'insert into #LDAP_AD_V2_Groups (sid,mgr,changeddate,createddate,distname,acctname,email,login) '
+ CHAR(13) + 'select objectSid,managedBy,whenChanged,whenCreated,distinguishedName,name,mail,samAccountName '
+ CHAR(13) + 'from openquery(ADSI,''' + @strADSISQL + ''' ) '
+ CHAR(13) + 'order by sAMAccountName'

-- PRINT @strSQL
EXEC SP_EXECUTESQL @strSQL


-- POPULATION OF USER > GROUPS RELATIONSHIP TABLE

IF EXISTS (SELECT *
FROM TEMPDB.DBO.SYSOBJECTS
WHERE ID = OBJECT_ID('tempdb.dbo.#CT'))
DROP TABLE #CT

-- Create a temporary table to hold AD user SID values
CREATE TABLE #CT (CT VARBINARY(85))

-- Declare and open a cursor to step through the list of Active Directory groups
DECLARE CURGROUPS CURSOR FOR
SELECT SID,
DISTNAME
FROM #LDAP_AD_V2_GROUPS
ORDER BY LOGIN

OPEN CURGROUPS

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN

WHILE @@FETCH_STATUS = 0
BEGIN
-- Empty the temp table
TRUNCATE TABLE #CT

-- Build a SQL statement to insert the SID values directly from the linked server into the temp table
SET @strsql = 'INSERT #CT
SELECT *
FROM OPENQUERY (
ADSI,
''SELECT objectSid
FROM ''''LDAP://' + @strDC + '''''
WHERE objectCategory = ''''User''''
AND memberof=''''' + REPLACE(@CN,'''','''''''''') + ''''''')'

EXEC( @strsql)

-- Select the number of records inserted. If this value is less than 1000 then there is no need
-- to execute the OLE calls, and we simply copy the values into the correlation table.
SELECT @CT = COUNT(* )
FROM #CT

--PRINT @ct
--PRINT @strsql

IF @CT <> 0
BEGIN
INSERT #LDAP_AD_V2_USERGROUPS
(USERLOGIN,
GROUPLOGIN)
SELECT CT,
@Login
FROM #CT
WHERE CT IS NOT NULL
END

FETCH NEXT FROM CURGROUPS
INTO @Login,
@CN
END

CURSORERROR:
CLOSE CURGROUPS
DEALLOCATE CURGROUPS
DROP TABLE #CT

/*
-- Individual SQL Statements to see users, groups & relationships
select * from #LDAP_AD_V2_Users
select * from #LDAP_AD_V2_Groups
select * from #LDAP_AD_V2_UserGroups
*/

-- Final Query to tie together the 3 tables and produce a report of group membership

SELECT #LDAP_AD_V2_GROUPS.ACCTNAME AS GROUPNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME AS USERACCOUNT
FROM #LDAP_AD_V2_USERGROUPS
INNER JOIN #LDAP_AD_V2_USERS
ON #LDAP_AD_V2_USERGROUPS.USERLOGIN = #LDAP_AD_V2_USERS.SID
INNER JOIN #LDAP_AD_V2_GROUPS
ON #LDAP_AD_V2_USERGROUPS.GROUPLOGIN = #LDAP_AD_V2_GROUPS.SID
where #LDAP_AD_V2_USERS.SAMACCOUNTNAME = ' username '
ORDER BY #LDAP_AD_V2_GROUPS.ACCTNAME,
#LDAP_AD_V2_USERS.SAMACCOUNTNAME

Format drives with correct allocation and offset for maximum SQL Server performance

Format drives with correct allocation and offset for maximum SQL Server performance

Setup Database Mail to use a Gmail, Hotmail, Yahoo or AOL account

Setup Database Mail to use a Gmail, Hotmail, Yahoo or AOL account