Search for Table Columns Using the System Schema

Josh,

So you want to search if a column exists in another table, but can't remember which table.  Here's a handy script to be able to search a specific schema for a certain column name:

Select Into Creates Identity Column on New Table

Josh,

Say you have a table like this:

CREATE TABLE MyOldTable (MyId INT IDENTITY(1,1),Name VARCHAR(100)
)

Then, you need to create another table from this for simplicity sake:

SELECT
     MyId,
     Name,
     row_number() OVER(ORDER BY Name DESC) AS MyNewIdINTO
     MyNewTableFROM
     MyOldTable

Looking at the "MyNewTable", you'll notice the MyId column is defined as an INT IDENTITY(1,1)!  Most likely you didn't intend for this to happen.  The fix is pretty simple:

SSAS Binding Too Small

Josh,

So you've updated some tables used in a cube by increasing a column's length.  When you go to process the cube you are getting an error like "Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated."

Seems easy enough, right?  You just need to increase the Dimension Attribute's length.  Make sure you do it for both the NameColumn and KeyColumns sections:

Thanks to http://shahfaisalmuhammed.blogspot.com/2012/03/size-specified-for-binding-too-small.html

Maximum Column Content Lengths

Josh,

So you are working on loading data into a table from another table and you're getting truncation errors.  To keep the destination table as small as possible for speed, you need to find out which column is causing the truncation error and how much you need to increase the destination columns size.

Let's use the handy SYS schema to help us out...

Reviewing SQL Schema Dates

Josh,

So you're wanting to know some basic dates around a schema in SQL Server.  Unfortunately the "sys.schemas" view doesn't have anything to indicate create or modify dates.  Furthermore if the schema creation isn't logged, then the only way to review this is to relate the schema to the "sys.objects" view.