SQL Server: Some Useful Schema Scripts

Here are some helpful scripts that help me to find out more about the table's I'm working on.

Getting more information about a table.  This stored procedure returns the table's columns, primary key, foregin keys, contrstraints, which tables reference this one, and more.


Getting the SQL for a view (or a trigger or stored procedure). Here is another stored procedure that fetches the create statement for various objects.

Getting the triggers on a table. If you ever need to know what triggers are executing on a table this one is very helpful, especially when also used with the query to get the SQL for a trigger above.

Getting the triggers on a table II. Here is another way to get the triggers which seems to work better.

Getting a table's columns. While this does basically repeat the functionality of the first query, it does introduce INFORMATION_SCHEMA, which contains lots of information about your schema in general.
Getting a table's indexes

And also while I'm listing some useful things for SQL Server, here are a couple links for some useful sql formatters.

• Instant SQL Formatter is the one I use the most
• TSQL Code Formatter is also pretty handy, although the query needs to compile for it to format it (so no parameters)

And here some more more useful SQL Server tools from SO.

Hibernate: @ElementCollection Example

Here is a small example of how to pull in a List of a single field from a separate table into a mapped entity.


Or you can also pull in a list of objects using the @Embeddable annotation, like this.

And here is the reference to the Hibernate Docs for more info.

SQL Server 2008- Unique Value on One Record

In the database schema I'm working on we need one record to have a unique value (in our case 'Y'), while the other records need a non unique value ('N') i.e.  only one record can have 'Y'.

To do that we can use an index like this:

Get a random row on SQL Server

Pretty simple method for getting a random row on SQL Server.
SELECT TOP 1 column FROM table ORDER BY NEWID()

More ways to get random rows on other databases can be seen here.
As well as another article on random rows on SQL Server.