SSAS Mining Structure Data Types

Microsoft Reference

When we create a mining model or a mining structure in Microsoft SQL Server Analysis Services, we must define the data types for each of the columns in the mining structure. The data type tells the analysis engine whether the data in the data source is numerical or text, and how the data should be processed. For example, if our source data contains numerical data, we can specify whether the numbers be treated as integers or by using decimal places.

Analysis Services supports the following data types for mining structure columns:

Data Type Supported Content Types
Text Cyclical, Discrete, Discretized, Key Sequence, Ordered, Sequence
Long Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered, Sequence, Time

Classified

Boolean Cyclical, Discrete, Ordered
Double Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered, Sequence, Time

Classified

Date Continuous, Cyclical, Discrete, Discretized, Key, Key Sequence, Key Time, Ordered

You can find details for discrete vs discretized here.

Advertisements

Power BI Report Server on premise version build release info

Microsoft Power BI Reference

  • Power BI Report Server
    • Version 1.1.6582.41691 (Build 14.0.600.442), Released: January 10, 2018
      • Security Updates
      • Bug Fixes
        • Fix for Model.GetParameters returning 400
        • Fix for setting shared data set to existing Paginated Reports (RDL)
        • Fix for ExecutionNotFoundException when exporting report with different parameter values to PDF
    • Version 1.1.6551.5155 (Build 14.0.600.438), Released: December 11, 2017
      • Bug Fixes
        • Failure to save data after refreshing for certain Power BI Desktop reports.
    • Version 1.1.6530.30789 (Build 14.0.600.437), Released: November 17, 2017
      • Bug Fixes
        • Fix for Basic Authentication Scenarios
        • Fix for weekdays were not selectable on schedule page for Subscriptions, Cache Refresh Plans and History Snapshots on Portal
        • For Paginated Reports (RDL), fix for having expressions in Textbox with CanGrow property set to false is resulting in values not showing colors and fonts not being proper
        • For Power BI Reports (PBIX), fix for adding Legends to line chart renders an empty visual
    • Version 1.1.6514.9163 (Build 14.0.600.434), Released: November 1, 2017
      • Bug Fixes
        • Fix for upload reliability problems for PBIX reports over 500MB
        • Fix for data loading issue for PBIX reports over 1GB
    • Version 1.1.6513.3500 (Build 14.0.600.433), Released: October 31, 2017
      • Features
        • Embedded Data Model Support
        • Excel Workbook Viewing (with Office Online Server integration enabled)
        • Scheduled Data Refresh (PBIX)
        • Direct Query Support
        • Large File Support (up to 2 GB)
        • Public REST API
        • Shared Dataset support in Power BI Desktop (via oData)
        • URL Parameter Support for PBIX files
        • Accessibility improvements
  • Power BI Desktop (optimized for Power BI Report Server)
    • Version: 2.51.4885.2501 (October 2017), Released: January 10, 2018
      • Security Updates
    • Version: 2.51.4885.1423 (October 2017), Released: November 17, 2017
      • Bug Fixes
        • Fix for 32-bit Power BI Desktop failing to run on x86 OS
        • For Power BI Reports (PBIX), fix to show x-axis gridlines
        • Other minor bug fixes
    • Version: 2.51.4885.1041 (October 2017), Released: October 31, 2017
      • Features
        • Contains changes required for connection with Power BI Report Server (October 2017)

PeopleSoft Campus Solutions 9.0 Understanding Academic Structure

PeopleSoft Campus Solutions 9.0 – Academic Structure

The academic structure and its elements are the building blocks for an academic institution. This diagram illustrates an institution’s academic structure at a high level:

To set up campuses, use the Campus Table component (CAMPUS_TABLE).

A campus is an entity, usually associated with a separate physical administrative unit, that belongs to a single academic institution, uses the same course catalog, and produces a common transcript for students within the same academic career. The PeopleSoft Campus Solution system enables you to define as many campuses within an academic institution as necessary to meet your business needs.

To set up academic careers, use the Academic Career Table component (ACAD_CAREER_TBL).

Academic career is a concept used in Campus Solutions to designate all course work undertaken by a student at an academic institution; you group this course work in a single student record. For example, a university that has an undergraduate school, a graduate school, and several professional schools can define an undergraduate career, graduate career, and a separate career for each professional school (for example, law, medical or dental). You might also make extended education or continuing education its own academic career, or make separate academic careers for every school or college at the undergraduate level.

Academic careers have these common characteristics:

  • All credit is granted under a common unit type, such as semester hours or quarter hours.
  • A single repeat scheme is used.

 

In SSRS Report How to hide or collapse detailed rows or sub-grouped rows

In SSRS Report how to show classic pivot like Excel report.

In other words how to hide or collapse detailed rows or sub-grouped rows.

Say we have report which shows Estimated Sales and Actual Sales by State, Region and Country.

We have report without any groups as:

Country  Region  State  Estimated-Sales  Actual-Sales.

In this report we need to create Groups as follows:

Country  => Region  => State.

NOTE: While creating groups you can select for Country Parent group and for remaining Child group.

When by default the SSRS report loads what we want is to hide entire row with Country is collapsed and will show when it is expanded.

Make sure it is Tablix.

Except parent’s group properties, navigate to each of the child’s group properties => Visibility section and DO AS FOLLOWS:

  1. select Hide option.
  2. Check on Display can be toggled by this report item. Please select the PREVIOUS ITEM.

 

 

SQL Server profile name is not valid [SQLSTATE 42000] Error 14607

Created first account and then profile for SQL Server Emailing.

I opened SQL management Studio and ran the test script:

EXEC msdb.dbo.sp_send_dbmail @recipients=’abc@xyz.com’, @profile_name = ‘ProfilenameABC’,    @subject = ‘test email ‘,    @body = ‘test email’,    @body_format = ‘HTML’ ;

It worked fine.

But the developer from my team came back saying the job has failed with error:

profile name is not valid [SQLSTATE 42000] Error 14607.

He too is getting the same error.

So then we realized we have to change it security and make it Public.

So in SQL management Studio, navigate to Database mail => Configure Database mail, and select option=> Manage Profile Security.

Making it public profile allows guest access too.

 

SQL Server Catalog Views Information Schema Views Metadata List of all tables views databases

To get metadata in SQL server such as list of all databases, or tables, views residing in a database we can use:

  • Catalog Views
  • Information Schema Views.
  • OLE DB schema rowsets.
  • ODBC catalog functions.
  • System stored procedures and functions.

In this blog let’s look at the first two approaches of Catalog Views, and Information Schema Views.

Metadata means data about data. 

The core of the SQL Server system catalogs is a set of views that show metadata that describes the objects in an instance of SQL Server. Metadata is data that describes the attributes of objects in a system.

Microsoft recommends using catalog views to access metadata for the following reasons:

  • All metadata is made available as catalog views.
  • Catalog views present metadata in a format that is independent of any catalog table implementation, therefore catalog views are not affected by changes in the underlying catalog tables.
  • Catalog views are the most efficient way to access core server metadata.
  • Catalog views are the general interface to catalog metadata and provide the most direct way to obtain, transform, and present customized forms of this metadata.
  • Catalog view names and the names of their columns are descriptive. Query results match what might be expected by a user who has a moderate knowledge of the feature that corresponds to the metadata that is being queried.

For example, the following query uses the sys.objects catalog view to return all database objects that have been modified in the last 10 days.

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() – 10
ORDER BY modify_date;

Information schema views are based on catalog view definitions in the ISO standard. They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables. Applications that use these views are portable between heterogeneous ISO compliant database systems.
Following are some of the catalog based queries published on Microsoft site.

Data Types

Tables, Indexes, Views, and Constraints

Modules (Stored Procedures, User-Defined Functions, and Triggers)

Schemas, Users, Roles, and Permissions

Answers

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 <database_name> with a valid database name.

USE AdventureWorks2008;
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.

USE <database_name>;
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 <database_name> and <schema_name> with valid names.

USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
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) = '<schema_name>' 
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 = '<schema_name>';
GO


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

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

USE <database_name>;
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.

USE <database_name>;
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


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

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

USE <database_name>;
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


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

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

USE <database_name>;
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.

NoteNote
This query does not return the name of the columns.
USE <database_name>;
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


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

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>')
ORDER BY c.column_id;
GO


How do I find the dependencies on a specified function?

Before you run the following query, replace <database_name> and <schema_name.function_name> with valid names.

USE <database_name>;
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('<schema_name.function_name>')
ORDER BY OBJECT_NAME(object_id), COL_NAME(object_id, column_id);
GO 


 

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

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

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


 

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

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
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('<schema_name.object_name>')
ORDER BY schema_name, object_name, p.parameter_id;
GO


 

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

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

USE <database_name>;
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


 

How do I find all views in a database?

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

USE <database_name>;
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


 

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

Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;
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() - <n_days>
ORDER BY modify_date;
GO


 

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

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>') 
    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


 

How do I view the definition of a module?

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('<schema_name.object_name>');
GO


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

USE <database_name>;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
GO


 

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

SELECT definition
FROM sys.server_sql_modules;
GO


 

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

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>');
GO


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

USE <database_name>;
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('<schema_name.table_name>');
GO


 

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

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>');


 

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.

-- 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


 

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

Before you run the following query, replace <database_name><schema_name.table_name>, and <column_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>')
    AND referenced_minor_id = COLUMNPROPERTY(referenced_major_id, '<column_name>', '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 <database_name> with a valid name.

USE <database_name>;
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 <database_name> with a valid name and <schema_name.data_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.

USE <database_name>;
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('<schema_name.data_type_name>'); 
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.

USE <database_name>;
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('<schema_name.data_type_name>');
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 <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias type name. For more information about computed column dependencies, see Understanding SQL Dependencies.

USE <database_name>;
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('<schema_name.data_type_name>')
    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 <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias 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.

USE <database_name>;
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('<schema_name.data_type_name>')
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('<schema_name.data_type_name>')
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.

USE <database_name>;
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('<schema_name.data_type_name>');
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 <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined type name.

USE <database_name>;
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('<schema_name.data_type_name>')
    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 <database_name> with a valid name and <schema_name.data_type_name> with a valid, schema-qualified CLR user-defined typealias 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_dependencies catalog 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.

USE <database_name>;
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('<schema_name.data_type_name>')
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 <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>');
GO


TOP

 

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

Before you run the following query, replace <database_name> and <schema_name.table_name> with valid names.

USE <database_name>;
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('<schema_name.table_name>');
GO


TOP

 

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

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

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


Or

USE <database_name>;
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 = '<column_name>';
GO


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

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

USE <database_name>;
GO
SELECT * 
FROM sys.tables;
GO


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

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

USE <database_name>;
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


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

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

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


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

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

USE <database_name>;
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('<schema_name.object_name>');
GO


How do I find the definition of a view?

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('<schema_name.object_name>');
GO


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

USE <database_name>;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID('<schema_name.object_name>')) AS ObjectDefinition;
GO