Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bmahf
Starting Member
14 Posts |
 Posted - 2011-08-22 : 13:28:14
|
| I am actually programming csharp database access, and my process is supposed to examine the system tables for a database in order to write out the sql script necessary to create the whole database. I am trying to find out which system table in my database has information as to which indexes are clustered. I can find out what indexes I have set on tables, but haven't yet seen anything that specifies whether the given index is clustered or not. Can someone point to the table that holds this information?Thanks... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-22 : 13:40:07
|
| look into sys.indexes for type =1------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
bmahf
Starting Member
14 Posts |
Posted - 2011-08-22 : 13:49:12
|
| Great, thanks. I'm new to these system tables, so I'm a little lost. This helps a lot. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-22 : 13:58:19
|
| or for type_desc = 'Clustered'--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 14:04:40
|
| I expect I'm answering the wrong question, but wouldn't it be easier to use SSMS to just generate a script of your database for you?Strikes me as quite a lot of work to generate a script that creates tables, columns, indexes, constraints, and so on. Plus unusal things like like computed columns ... |
|
|
|
bmahf
Starting Member
14 Posts |
Posted - 2011-08-22 : 16:11:20
|
| In a perfect world yes, but for this project, someone will remotely request my software to create such a file for a specific set of data, so that they can take that home to look at it. They will in many cases not have the database created at home, so the script must first test for the existence of the database and then create the structure(s) as needed, and then it must do the inserts. Can SSMS be queried by my software to create such a script, given the table dependencies and the specific slice of data that I want to drop to file? If it can, I would rather do that. Otherwise, I have to specify the database structure for creation and then the inserts that are needed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-22 : 18:05:06
|
| "Can SSMS be queried by my software to create such a script, given the table dependencies and the specific slice of data that I want to drop to file?"Maybehttp://blogs.technet.com/b/heyscriptingguy/archive/2010/11/04/use-powershell-to-script-sql-database-objects.aspxI think that evaluating the "slice" of tables etc is fraught. How will you handle table dependencies (i.e. where the dependencies are outside the requested "slice"?Personally I think you would be better to:Create temporary databaseUse SELECT * INTO TargetDB.dbo.TargetTable FROM SourceTableBackup the temporary database to a fileProvide the user with the means to download that fileThen they don't have to mess around creating tables / running scripts etc., then can just restore from the database backup.They will need a sql version of the same version, or later, than the Source though. |
|
|
|
bmahf
Starting Member
14 Posts |
Posted - 2011-08-23 : 11:33:35
|
| That actually sounds like a good deal. Can I, from within C#, make a call to SQL Server and tell it to create a new database? Don't I still need the database definition? The selection of the data is simple. Not being a dba, I am not sure about the on-the-fly creation of the database in order to do the data slicing. Can you tell me how I can do this? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-08-23 : 13:31:14
|
"Can I, from within C#, make a call to SQL Server and tell it to create a new database?"Yup. CREATE DATABASE command will do that."Don't I still need the database definition?"Maybe. SELECT * INTO TargetTable FROM SourceTable WHERE ... will create a new table, called "TargetTable", with columns having the same data types etc. as the original table, and some properties, such as IDENTITY, preserved. Note that you can specify a target database too:SELECT * INTO TargetDB.dbo.TargetTable FROM ... However, you won't have any indexes or relationships on the newly created tables, so its imperfect in that sense.If you only want to transfer the structure of some tables, and the data they contain, it will do that job.I come back to my earlier point that if you want to transfer more "structure" about the tables in your "slice" you will come up against the problem of the tables having referential integrity to other tables which are not included in the slice. Its difficult, at that point, to know where to stop! So maybe getting tables and their their data in a "standalone database" is a start?You might want to ensure that you transfer some other properties / attributes - such as Primary Key - so that the user cannot, at least, create duplicate records.Then what?Indexes perhaps? Check and default constraints (although check constraints may reference tables not in the "slice").I think that would be enough for mini-projects to use as reference data - but you know your project better than I do of course  |
|
|
|
|
|
|
|
|
Which system table records clustered indexes