Category Archives: SQL

SQL

SQL Server: How to change the schema for a table

I was moving a few tables around in SQL server 2008 today (hence my earlier post about wanting to list everything), and wanted to organise things a little better, so I decided to move some of my tables to different schemas.

Here’s how I did it:-

ALTER SCHEMA newSchema TRANSFER dbo.theTable

This would transfer dbo.theTable to newSchema.theTable. Another handy little trick. Make sure the schema you are moving your table to (in my example ‘newSchema’) exists first though, or you’ll get errors.

SQL

SQL Server: A query to list table and schema names from a database

Earlier today I needed a list of all schemas and their tables in one of my SQL server 2008 databases.  After a bit of searching around and figuring things out I managed to put this together:-

USE [your database name here];

SELECT
    schemas.name [Schema name],
sysobjects.name [Table name]
FROM sysobjects
    INNER JOIN sys.schemas
        ON sysobjects.uid = sys.schemas.schema_id
WHERE xtype='U'
ORDER BY [Schema name], [Table name]

Not sure if it’s the best way of going about things, but it certainly seemed to do exactly what I wanted. Any comments or easier ways of doing this, maybe?

SQL

SQL Server: Show the date a table was last modified

I’m currently developing an online application using classic asp (no negative comments please), with the data layer provided by SQL server 2008.  One of the tables I use is refreshed from another source regularly but not every day, which means that the data displayed might not always be up to date.

I wanted a simple way to display the date the data was last updated so that I could let my users know that the data they are viewing is accurate as of that date.

Turns out this can be done with a query, and the SQL is really quite simple.  Here it is:-

SELECT modify_date FROM sys.tables WHERE name = 'YOUR TABLE NAME HERE';

This is really useful, especially if you want to let your users know when the last time modifications were made to your table.

I’ve read a number of other ways to do this, but I think this is the most simple and doesn’t require any kind of admin permissions on the server.  Good stuff!